Hi, I have a table like COL1 COL2 A 3 A 5 A 2 B 1 B 8 C 3 I need to have a sum(col2) group by col1 but, due to a requirement, I want to add 2 calculated columns COL1 COL2 COL3 COL4 A 3 8 0 A 5 8 0 B 1 9 0 B 8 9 0 C 3 0 3 COL3 will have the sum(col2) [ group by COL1] if sum(col2) > 5 [ which is threshold ] else 0 COL4 will have sum(col2) group by COL1 if sum(col2) < 5 threshold else 0 I need an update with case and grouping which is very hard :) Any suggestions?
SQL Statement
September 10th, 2015 6:20pm
;with cte as (select Col1, col2, sum(col2) over (partition by Col1) as Total from myTable)
select col1, col2, case when Total > 5 then Total else 0 end as Col3, case when Total <=5 then Total else 0 end as Col4 from cte
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 6:26pm
Thanks that worked magic COL1 COL2 COL3 COL4 A 3 8 0 A 5 8 0 B 1 9 0 B 8 9 0 C 3 0 3 Threshold is 5 ---------------------------------------- final dataset which I want is COL1 TOTAL THRESHOLD A 3 Above A 5 Below B 4 Above B 5 Below C 0 Above C 3 Below say in first dataset for COL1 =A If COL3 > 5 [ threshold ] then the second dataset will have a total [ 8-3 ] for Above 5 for below say in first dataset for COL1 =A If COL3 =0 second dataset Will have total 0 for Above COL4 for below I have this reporting requirement and this is the last thing holding it up
September 10th, 2015 7:56pm
hi
please guide on the output
cheers
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 3:15am