Group and Count Column Values - 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 2008 Forums

Transact-SQL (2008)

Group and Count Column Values

Author

Topic

randolphoralph
Starting Member

7 Posts

Posted - 2012-11-20 : 18:58:59

I am not sure how to make my query group and count if certain values are found in the OrderID column.

Here is what I have so far.

Select FirstName, LastName, Count(OrderID) as PT, Count(OrderID) as TO, Count(OrderID) as TS

From Tracking

Where OrderID in ('PT', 'TO', 'TS')

Group by FirstName, LastName

Here is an example of the table

FirstName LastName OrderID
Billy Smith PT
Billy Smith PT
Billy Smith TO
Sally Smith PT
Sally Smith TS
Sally Smith TS

Here is what I would like from the output of my query

FirstName LastName PT TO TS
Billy Smith 2 1 0
Sally Smith 1 0 2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 19:31:51

[code]
Select FirstName,LastName
,SUM(Case When OrderId = 'PT' then 1 else 0 End)as PT
,SUM(Case When OrderId = 'TO' then 1 else 0 End)as TO
,SUM(Case When OrderId = 'TS' then 1 else 0 End)as TS
from Tracking
Group by FirstName,LastName
Order by FirstName,LastName
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources