How to SUM a COUNT field? 1

[rawe]

Hi folks

I need some help on this SQL query:
I'm building a ticket selling system (for a non-profit music festival) and I would like to be able to see how many tickets were sold up to a specific date.

Right now I have:

SELECT 
  tblevent.EventID,
  tblevent.EventName,
  DATEDIFF(tblevent.StartDate,tblticket.BoughtDate) AS DateDifference,
  COUNT(tblticket.TicketID) AS CountTickets,
FROM
  tblticket
INNER JOIN
  tblevent ON (tblticket.Event_ID = tblevent.EventID)
GROUP BY
  DateDifference, EventID
ORDER BY
  DateDifference,EventName

This will give me the number of tickets sold per day per event (where the day is the number of days before the event/festival starts):

EventID EventName DateDifference CountTickets
7 Festival 1 12 1
10 Festival 2 13 6
7 Festival 1 14 4
10 Festival 2 14 24
7 Festival 1 15 9
10 Festival 2 15 9

But: I would like to be able to get the *accumulated* number of tickets sold per day per event:

EventID EventName DateDifference SUMCountTickets
7 Festival 1 12 14
10 Festival 2 13 39
7 Festival 1 14 13
10 Festival 2 14 33
7 Festival 1 15 9
10 Festival 2 15 9

Does anyone have any good ideas?

Best

Rasmus Wehner
Mimia, Denmark

 

[PHV]

Something like this ?
SELECT
E.EventID,
E.EventName,
DATEDIFF(E.StartDate,T.BoughtDate) AS DateDifference,
COUNT(T.TicketID) AS CountTickets,
(SELECT Count(*) FROM tblticket S WHERE S.Event_ID=T.Event_ID AND S.BoughtDate<=T.BoughtDate) AS SUMCountTickets
FROM
tblticket T
INNER JOIN
tblevent E ON (T.Event_ID = E.EventID)
GROUP BY
DateDifference, EventID
ORDER BY
DateDifference, EventName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rawe]

Hi PHV

Thank you very much for your reply.
The SQL script works just as I would like it to, so I'm one happy programmer right now. Great!

Best

Rasmus Wehner
Mimia, Denmark