Problem selecting count, comparing 2 fields in same table

[Chris]

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.

[David Portas]

The query you posted gives the result you say you want!

CREATE TABLE Table1 (group1 CHAR(3) NOT NULL, group2 CHAR(3) NOT NULL,
group1_result INTEGER NOT NULL, group2_result INTEGER NOT NULL /*, PRIMARY
KEY ??? */)

INSERT INTO Table1 VALUES ('One', 'Two', 3,2)
INSERT INTO Table1 VALUES ('One', 'Two', 3,1)
INSERT INTO Table1 VALUES ('One', 'Two', 2,5)
INSERT INTO Table1 VALUES ('One', 'Two', 4,1)
INSERT INTO Table1 VALUES ('One', 'Two', 0,5)

SELECT group1, group2,
SUM(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),
SUM(CASE WHEN group2_result > group1_result THEN 1 ELSE 0 END)
FROM Table1
GROUP BY group1, group2

What is the primary key of Table1? Did you leave out some relevant
column(s)?

--
David Portas
SQL Server MVP
--