You might look into WITH ROLLUP. That could easily give you cumulative totals for the
year, but off the top of my head I can't think
of a way to get it for the months.
----- Original Message -----
From: "Barry Newton" <bnewton@stripped>
To: <mysql@stripped>
Sent: Wednesday, August 02, 2006 10:29 PM
Subject: Running Totals?
> Back with another registration db question:
>
> Have a convention database which tracks people as they register all year long; the
> actual convention is held in October. I've got
> a fairly simple query which shows how many people registered in each calendar
> month--useful to compare to prior year to see if
> we're at least on track with our count.
>
> It would make life easier if I could also show a column with the cumulative count for
> each month. The existing output is:
>
> +-----------+------+---------------+----------+
> | Month | Year | Registrations | Monindex |
> +-----------+------+---------------+----------+
> | October | 2004 | 23 | 200410 |
> | December | 2004 | 5 | 200412 |
> | January | 2005 | 9 | 200501 |
> | February | 2005 | 11 | 200502 |
> | April | 2005 | 2 | 200504 |
> | May | 2005 | 48 | 200505 |
> | June | 2005 | 45 | 200506 |
> | July | 2005 | 10 | 200507 |
> | August | 2005 | 17 | 200508 |
> | September | 2005 | 58 | 200509 |
> | October | 2005 | 97 | 200510 |
> +-----------+------+---------------+----------+
>
> The cumulative column would ideally show 23,28,37, etc.
>
> Also, if anyone has a better way to keep the different years apart than the
> 'monindex' column, or at least to suppress displaying
> it, I'll be really interested.
>
> The existing query is:
>
> Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations,
> Extract(Year_Month from DatePaid) Monindex
> From capclave2005reg
> Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y')
> Group by Monindex
>
> Union
>
> Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations,
> Extract(Year_Month from DatePaid) Monindex
> From capclave2005reg
> where year(DatePaid)=2005 and (amount > 0 or Dealer = 'Y')
> Group by Monindex;
>
>
>
>
> Barry
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
