Barry

>It would make life easier if I could also show a column
>with the cumulative count for each month. 

Set @cum - 0;
Select
  Monthname(DatePaid) Month,
  Year(DatePaid) Year,
  Count(*) as Registrations,
  Extract(Year_Month from DatePaid) AS Monindex,
  @cum := @cum + Count(*) AS 'Year to date'
From capclave2005reg
Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y')
Group by Monindex ;

PB

-----

Barry Newton wrote:
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