I have the following table structure.
group1 group2 group1_result group2_result
'One' 'Two' 3 2
'One' 'Two' 3 1
'One' 'Two' 2 5
'One' 'Two' 4 1
'One' 'Two' 0 5
I need to sum up the number of times 'One' is greater than 'Two', and
vice-versa. For example, the result I would like to achieve is as
follows.
group1 group2 group1_total group2_total
'One' 'Two' 3 2
I'm using the following SQL statement, but I get 5 rows returned,
giving me a '1' or '0' for each row.
select group1, group2
,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END)
,sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)
FROM table1
GROUP BY group1, group2
Any help would be greatly appreciated.