Complex count statement - 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)

Complex count statement

Author

Topic

Farquaad
Starting Member

2 Posts

Posted - 2012-02-28 : 09:27:36

Hello!

Is it possible to make a count, that counts specified row values pr distinct customer row? The thing that puzzles me, is how to include a where clause for the TicketType column since I only want to count incidents and problems.

Assuming the following table input:

| Customer | TicketType |
|----------|------------|
| Cust1 | Incident |
| Cust2 | Incident |
| Cust2 | Problem |
| Cust3 | Problem |
| Cust1 | Other |
| Cust2 | AndAnother |
|-----------------------|

This is the output I am aiming for:

| Customer | Incidents | Problems |
|----------|-----------|----------|
| Cust1 | 1 | 0 |
| Cust2 | 1 | 1 |
| Cust3 | 0 | 1 |
|---------------------------------|

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-28 : 09:37:22

select
Customer,
sum(case when TicketType='Incident' then 1 else 0 end) as Incidents,
sum(case when TicketType='Problem' then 1 else 0 end) as Problems
from YourTable
group by Customer

No, you're never too old to Yak'n'Roll if you're too young to die.