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
|
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
