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'
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
;
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.