== I need help making a query ==

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)

== I need help making a query ==

Author

Topic

xylenz
Starting Member

2 Posts

Posted - 2007-08-24 : 19:46:45

I have a table that contains the following data:

ID Stratum Group
1 A 1
2 M 3
3 X 2
4 A 2
5 X 1
6 M 3
7 X 2
8 A 1

There are only three groups. ID is unique but i dont really care about it. I need to make a query on this table that will return this:

Stratum Group1 Group2 Group3 Total
A 2 1 0 3
M 0 0 2 2
X 1 2 0 3

This result table shows the count of how many rows in each stratum belongs to each group. Since there are two rows where Stratum=A and Group=1, the table would report 2 under Group1 in the A row. Any clue?

Thanks!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-25 : 01:07:05

This will do it... obviously, you'll need to change the table name...


 SELECT Stratum,
        Group1 = SUM(CASE WHEN [Group] = 1 THEN 1 ELSE 0 END),
        Group2 = SUM(CASE WHEN [Group] = 2 THEN 1 ELSE 0 END),
        Group3 = SUM(CASE WHEN [Group] = 3 THEN 1 ELSE 0 END),
        Total  = COUNT(*)
   FROM yourtable
  GROUP BY Stratum

--Jeff Moden

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-25 : 09:20:09

quote:

Originally posted by Jeff Moden

This will do it... obviously, you'll need to change the table name...


 SELECT Stratum,
        Group1 = SUM(CASE WHEN [Group] = 1 THEN 1 ELSE 0 END),
        Group2 = SUM(CASE WHEN [Group] = 2 THEN 1 ELSE 0 END),
        Group3 = SUM(CASE WHEN [Group] = 3 THEN 1 ELSE 0 END),
        Total  = COUNT(*)
   FROM yourtable
  GROUP BY Stratum

--Jeff Moden

Wow, I didn't realize you could do that in the select statement, what is that called so I can do some more research into the functionality? I am referring to the x=sum(CASE part of the select statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-25 : 11:17:43

I think it is called CONDITIONAL in Books Online. It is listed together with IF.

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-27 : 01:28:36

Actually, it's called a "Crosstab"...

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 02:08:44

quote:
Originally posted by Zoroaster

quote:
Originally posted by Jeff Moden

This will do it... obviously, you'll need to change the table name...

SELECT Stratum,
Group1 = SUM(CASE WHEN [Group] = 1 THEN 1 ELSE 0 END),
Group2 = SUM(CASE WHEN [Group] = 2 THEN 1 ELSE 0 END),
Group3 = SUM(CASE WHEN [Group] = 3 THEN 1 ELSE 0 END),
Total = COUNT(*)
FROM yourtable
GROUP BY Stratum

--Jeff Moden

Wow, I didn't realize you could do that in the select statement, what is that called so I can do some more research into the functionality? I am referring to the x=sum(CASE part of the select statement.

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

xylenz
Starting Member

2 Posts

Posted - 2007-08-27 : 13:41:36

quote:

Originally posted by Jeff Moden

This will do it... obviously, you'll need to change the table name...


 SELECT Stratum,
        Group1 = SUM(CASE WHEN [Group] = 1 THEN 1 ELSE 0 END),
        Group2 = SUM(CASE WHEN [Group] = 2 THEN 1 ELSE 0 END),
        Group3 = SUM(CASE WHEN [Group] = 3 THEN 1 ELSE 0 END),
        Total  = COUNT(*)
   FROM yourtable
  GROUP BY Stratum

--Jeff Moden

Wow! Thanks a LOT! You're a life saver!

One tweak though. The total line works better like this:


Total = COUNT(Group)

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-27 : 14:19:29

You're very welcome. Thank you for the feedback

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources