Need Help With GROUP BY Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help With GROUP BY Statement

Author

Topic

themark712
Starting Member

5 Posts

Posted - 2007-05-01 : 09:59:06

I need to come up with a SQL Server statement that will return three rows: a grouped text row, and two value rows based on two numeric rows within the table. I can best explain by giving the table layout.

tblTable:
Text1     Value1     Value2
========= ========== ==========
a         3          9
a         4          1
b         7          3
b         6          5
c         3          6
c         1          5

Basically, I need a query that will return the Text1 value, the number of rows where Value1 is greater than Value2 and the number of rows where Value2 is greater than Value1, both grouped by Text1. So the results would look like this.

Results:
Text1     Expr1 (Value1 > Value2)   Expr2 (Value2 > Value1)
========  ========================  ========================
a         1                         1
b         2                         0
c         0                         2

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 10:18:29

[code]
select Text1,
[Expr1 (Value1 > Value2)] = sum(case when Value1 > Value2 then 1 else 0 end),
[Expr2 (Value2 > Value1)] = sum(case when Value2 > Value1 then 1 else 0 end)
from tblTable
group by Text1
order by Text1
[/code]

themark712
Starting Member

5 Posts

Posted - 2007-05-01 : 10:23:23

LOL ... I just saw your reply here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82905

... and got it.

Thanks ... works like a charm!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 10:23:47

Yup. Similar concept.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources