sql query - group by - SQL Server Forums

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)

sql query - group by

Author

Topic

badinar
Starting Member

14 Posts

Posted - 2010-11-18 : 12:02:26

i have a table similar to the one below.

user | survey | result
------------------------
1 1 O
1 2 Y
1 3 N
1 4 Y
1 5 O
1 6 O
2 1 Y
2 2 Y
2 3 N
2 4 Y
3 1 O
3 2 Y
3 3 O
3 4 Y
3 5 O

i'm trying to create a query to get the following result

user | total survey | Open | Yes | No
-----------------------------------------
1 6 3 2 1
2 4 0 3 1
3 5 3 2 0

any one already done this one before?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:05:43

[code]
SELECT user,
COUNT(survey) AS totalsurvey,
COUNT(CASE WHEN result='O' THEN 1 ELSE NULL END) AS Open,
COUNT(CASE WHEN result='Y' THEN 1 ELSE NULL END) AS Yes,
COUNT(CASE WHEN result='N' THEN 1 ELSE NULL END) AS No
FROM Table
GROUP BY user
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

badinar
Starting Member

14 Posts

Posted - 2010-11-18 : 12:09:52

charm!!!! charm!! charm!!!

thanks a lot visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:10:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 03:23:00

Avoid unwanted warnings

SELECT user,
COUNT(survey) AS totalsurvey,
SUM(CASE WHEN result='O' THEN 1 ELSE 0 END) AS Open,
SUM(CASE WHEN result='Y' THEN 1 ELSE 0 END) AS Yes,
SUM(CASE WHEN result='N' THEN 1 ELSE 0 END) AS No
FROM Table
GROUP BY user

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources