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]