From: Peter Brawley
Date: August 3 2006 3:10am
Subject: Re: Running Totals?
List-Archive: http://lists.mysql.com/mysql/200555
Message-Id: <44D16936.6020307@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44D169362B7F======="
--=======AVGMAIL-44D169362B7F=======
Content-Type: multipart/alternative; boundary=------------050001070301010807040902
--------------050001070301010807040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
>
>
--------------050001070301010807040902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------050001070301010807040902--
--=======AVGMAIL-44D169362B7F=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-44D169362B7F=======--