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?

;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

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