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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
