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