Summing max values from a query result

Hi all,

SQL newbie with a problem i'm struggling to overcome. I need to find the maximum values from a dataset based on a variable and then summate them.

Unfortunately my original dataset is structured in a strange way and so i have to write a join query to get it in a sensible way to begin with. my query and the result is below.

What i then need to do is summate the maximum values in column 'Volume' based on the variables in 'Mix Ref' so in this instance the answer would be 24+4+16 = 44

Query
SELECT tab1.formid,[Mix Ref],[Volume]
FROM
(SELECT [formid],[row],[Value] As [Mix Ref]
FROM [FieldViewAPIData].[dbo].[formTableGroup]
where formtemplatelinkid = '1725434'
and [row]<>0
and [column] = 4) tab1
LEFT JOIN
(SELECT [formid],[row],[Value] AS [Volume]
FROM [FieldViewAPIData].[dbo].[formTableGroup]
where formtemplatelinkid = '1725434'
and [row]<>0
and [column] = 5) tab2
ON tab1.formid = tab2.formid AND tab1.[row]=tab2.[row]
where tab1.formid = 'f11991.213'

Result
formid Mix Ref Volume
F11991.213 135/3 8
F11991.213 135/3 16
F11991.213 135/3 24
F11991.213 135/1 4
F11991.213 pt8 8
F11991.213 pt8 16

I appreciate this may not be asked in the best way but any and all help is appreciated.

Ed

Sorry, that result table isn't clear maybe this is slightly better

Result
formid - Mix Ref - Volume
F11991.213 - 135/3 - 8
F11991.213 - 135/3 - 16
F11991.213 - 135/3 - 24
F11991.213 - 135/1 - 4
F11991.213 - pt8 - 8
F11991.213 - pt8 - 16

you can format it with the </> button, or using

    ```text

    ... Your results here ...

    ```

and also :

    ```sql

    ... Your SQL code here ...

    ```

Sorry, but not sure I haven fully understood your question, but can you just WRAP your SQL to then get the MAX value you need?

SELECT formid,[Mix Ref], MAX([Volume])
FROM
(
SELECT ... your whole query here
) AS X
GROUP BY formid,[Mix Ref]
ORDER BY formid,[Mix Ref]
1 Like

Try this:

select formid
      ,sum(volume) as volume
  from (select formid
              ,max(case when [column]=4 then [value] else null end) as mix_ref
              ,max(cast(case when [column]=5 then [value] else null end as int)) as volume
              ,row_number() over(partition by max(case when [column]=4 then [value] else null end)
                                     order by max(cast(case when [column]=5 then [value] else null end as int)) desc
                                )
               as rn
          from fieldbiewapidata.dbo.formtablegroup
         where formid='f11991.213'
           and formtemplatelinkid='1725434'
           and [row]<>0
           and [column] in (4,5)
         group by formid
                 ,formtemplatelinkid
                 ,[row]
       ) as a
 where rn=1
 group by formid
;
1 Like

Kristen,

Thanks for your help and sorry for my slow reply but I've been off for a few days. this doesn't quite do what I need as it doesn't perform the summation.

Again, I appreciate your reply.

Ed

bitsmed,

sorry for my slow response but I've been away for a few days. This does the trick perfectly. Thank you very much.

Ed