Summarizing data - 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)

Summarizing data

Author

Topic

buster.t
Starting Member

1 Post

Posted - 2007-11-02 : 11:11:00

Hello,

I have two tables. One table represents a ticket: Ticket (ID, DateSold). Another table represents the status of a ticket: TicketStatus (ID, TicketID, StatusID)

I need to create a query that can tell me how many tickets were sold in each month. But then, I need to be able to break that monthly ticket total down by StatusID. I have the following SQL Statement:

SELECT
Count(1) as 'TicketCount',
Month(DateSold) as 'SellMonth'
FROM
Ticket t
GROUP BY
Month(DateSold)

Which works up to the first part of the problem. However, how do I get the number sold per each status? Essentially, I would like my result set to look like the following (pipe-delimited):

TicketCount | SellMonth | Status1Total | Status2Total
5 | 1 | 2 | 3
9 | 2 | 8 | 1
7 | 3 | 3 | 4

Can somebody please help? Thank you!

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 11:27:13

if you have a defined set of status values you can use something like the below, otherwise you should look into using a PIVOT statement
SELECT
COUNT(DISTINCT dbo.Ticket.ID) AS TicketCount
,MONTH(dbo.Ticket.DateSold) AS SellMonth
,SUM(CASE WHEN dbo.TicketStatus.StatusID = 1 THEN 1 ELSE 0 END) AS Status1
,SUM(CASE WHEN dbo.TicketStatus.StatusID = 2 THEN 1 ELSE 0 END) AS Status2
,SUM(CASE WHEN dbo.TicketStatus.StatusID IS NULL THEN 1 ELSE 0 END) AS StatusNotSet
,SUM(CASE WHEN dbo.TicketStatus.StatusID NOT IN (1,2,3) THEN 1 ELSE 0 END) AS StatusUnknown
FROM
dbo.Ticket
LEFT JOIN
dbo.TicketStatus
ON dbo.TicketStatus.TicketID = dbo.Ticket.ID
GROUP BY
MONTH(dbo.Ticket.DateSold)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources