CuriousCode


Hello,

I'm not a very strong SQL coder. I am having a problem regarding summarizing some data. I am hoping somebody can provide me with some direction.

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!




Re: Problem Summarizing Data

anomolous


You need to use a sum and a Case statement. Try this:

Select

Count(*) as 'TicketCount',

Month(DateSold) as 'SellMonth',

Sum( Case When StatusID = 0 Then 1 End ) as 'Status0',

Sum( Case When StatusID = 1 Then 1 End ) as 'Status1' -- etc. as needed

From Ticket t Join TicketStatus ts

on t.ID = ts.ID

Group by

Month(DateSold)

If you want to combine several status codes into a single status count, then use an IN construct or an inequality rather than =.

Hope this gets you started.






Re: Problem Summarizing Data

CuriousCode

Hi Anomolous,

Thank you for your quick response. The problem is, the SUM statement does not aggregate all of the entries from TicketStatus with the specific StatusID. It's like it only see if there is one or not. How can I expand on this query to summarize all of the TicketStatus information

Thank you!






Re: Problem Summarizing Data

hunchback

If there are few statuses, then you can use function CASE:

SELECT
Count(*) as 'TicketCount',
Month(t.DateSold) as 'SellMonth',

sum(case when ts.StatusID = 1 then 1 else 0 end) as Status_1,

sum(case when ts.StatusID = 2 then 1 else 0 end) as Status_2

FROM
Ticket t inner join TicketStatus as ts on t.ID = ts.TicketID
GROUP BY
Month(t.DateSold)

or you can use the new operator PIVOT, if you are working with SS 2005. If there are many statuses, then use dynamic sql , but keep an eye on SQL Injection.

DECLARE @statuses NVARCHAR(MAX)

DECLARE @sql NVARCHAR(MAX)

SET @statuses = STUFF(

(

SELECT ',' + QUOTENAME(StatusID)

FROM (SELECT DISTINCT StatusID FROM dbo.TicketStatus) AS t

ORDER BY StatusID

FOR XML PATH('')

), 1, 1, '')

SET @sql = N'

SELECT

a.*,

b.cnt

FROM

(

SELECT

*

FROM

(

SELECT

MONTH(t.DateSold) AS [Month],

t.[ID],

ts.StatusID

FROM

dbo.Ticket AS t

INNER JOIN

dbo.TicketStatus AS ts

ON t.ID = ts.TicketID

) AS t

PIVOT

(

COUNT([ID])

FOR [StatusID] IN (' + @statuses + N')

) AS pvt

) AS a

INNER JOIN

(

SELECT

MONTH(t.DateSold) AS [Month],

COUNT(*) AS cnt

FROM

dbo.Ticket AS t

INNER JOIN

dbo.TicketStatus AS ts

ON t.ID = ts.TicketID

GROUP BY

MONTH(t.DateSold)

) AS b

ON a.[Month] = b.[Month]

ORDER BY

b.[Month]

'

EXEC sp_executesql @sql

GO

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

PIVOT on Steroids

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

AMB





Re: Problem Summarizing Data

CuriousCode

Hello,

Thank you very much for your help. And I apologize as I believe I am not clearly stating my problem. Let's pretend I have the following results sets (pipe-delimited):

Ticket (ID, DateSold)

------------------------------

1 | 11/02/2007

2 | 11/08/2007

3 | 01/02/2008

4 | 12/02/2007

5 | 01/02/2008

6 | 01/02/2008

TicketStatus (ID, TicketID, StatusID)

-----------------------------------------------------

1 | 1 | 3

2 | 3 | 1

3 | 4 | 1

5 | 2 | 3

6 | 6 | 3

The fourth record from TicketStatus is intentionally removed.

I need to create a query that would generate the following result set

Results (MonthNumber, TotalTicketsSoldInMonth, Status1Tickets, Status3Tickets)

-----------------------------------------------------------------------------------------------------------------------

11 | 2 | 1 | 1

12 | 1 | 1 | 0

01 | 3 | 1 | 1

What am I overlooking I feel like there is some small detail that I am not understanding Thank you very much for your help.





Re: Problem Summarizing Data

Redbaron71

Hi Curious Code,

I think this should give the result that you are expecting. I did not create the table and populate it but I think this should work.

Declare table #Table (MonthGrp int, StatusID int, StatusCount int)

Insert into #Table(MonthGrp, StatusID, StatusCount)
SELECT
Month(DateSold) as 'SellMonth',
StatusID,
Count(*) as 'StatusCount'
FROM
Ticket t
JOIN TicketStatus ts
on t.ID = ts.ID
GROUP BY
Month(DateSold), statusID


Select TotalCount, SellMonth, isNULL(b.StatusCount,0) as Status1Tickets, isNULL(c.StatusCount,0) as Status3Tickets
(Select Distinct Month(DateSold) as SellMonth,Count(*) as TotalCount from
Ticket t
GROUP BY
Month(DateSold)) as a
LEFT OUTER JOIN
(Select SellMonth, StatusCount from #Table
where StatusID = 1) as b
on a.SellMonth = b.SellMonth
LEFT OUTER JOIN
(Select SellMonth, StatusCount from #Table
where StatusID = 3) as c
on a.SellMonth = c.SellMonth))





Re: Problem Summarizing Data

hunchback

It will be easier for us, if you post the structure of the tables, including constraints and indexes, sample data in the form of "insert" statements and expected result. The help should be mutual, shouldn't it

Code Block

CREATE TABLE dbo.Ticket (

ID int,

DateSold datetime

)

GO

CREATE TABLE dbo.TicketStatus (

ID INT,

TicketID INT,

StatusID INT

)

GO

SET NOCOUNT ON

INSERT INTO dbo.Ticket VALUES(1, '11/02/2007')

INSERT INTO dbo.Ticket VALUES(2, '11/08/2007')

INSERT INTO dbo.Ticket VALUES(3, '01/02/2008')

INSERT INTO dbo.Ticket VALUES(4, '12/02/2007')

INSERT INTO dbo.Ticket VALUES(5, '01/02/2008')

INSERT INTO dbo.Ticket VALUES(6, '01/02/2008')

INSERT INTO dbo.TicketStatus VALUES(1, 1, 3)

INSERT INTO dbo.TicketStatus VALUES(2, 3, 1)

INSERT INTO dbo.TicketStatus VALUES(3, 4, 1)

INSERT INTO dbo.TicketStatus VALUES(5, 2, 3)

INSERT INTO dbo.TicketStatus VALUES(6, 6, 3)

SET NOCOUNT OFF

GO

DECLARE @statuses NVARCHAR(MAX)

DECLARE @sql NVARCHAR(MAX)

SET @statuses = STUFF(

(

SELECT ',' + QUOTENAME(StatusID)

FROM (SELECT DISTINCT StatusID FROM dbo.TicketStatus) AS t

ORDER BY StatusID

FOR XML PATH('')

), 1, 1, '')

SET @sql = N'

SELECT

a.*,

b.cnt

FROM

(

SELECT

*

FROM

(

SELECT

MONTH(t.DateSold) AS [Month],

t.[ID],

ts.StatusID

FROM

dbo.Ticket AS t

LEFT OUTER JOIN

dbo.TicketStatus AS ts

ON t.ID = ts.TicketID

) AS t

PIVOT

(

COUNT([ID])

FOR [StatusID] IN (' + @statuses + N')

) AS pvt

) AS a

INNER JOIN

(

SELECT

MONTH(t.DateSold) AS [Month],

COUNT(*) AS cnt

FROM

dbo.Ticket AS t

LEFT OUTER JOIN

dbo.TicketStatus AS ts

ON t.ID = ts.TicketID

GROUP BY

MONTH(t.DateSold)

) AS b

ON a.[Month] = b.[Month]

ORDER BY

b.[Month]

'

EXEC sp_executesql @sql

GO

DROP TABLE dbo.[Ticket], dbo.[TicketStatus]

GO

Result:

Month 1 3 cnt

1 1 1 3

11 0 2 2

12 1 0 1

AMB