SQLTeam.com | Weblogs | Forums

Summing max values from a query result


#1

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


#2

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


#3

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]

#4

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
;

#5

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


#6

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