List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 3 2006 3:10am
Subject:Re: Running Totals?
View as plain text  
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
>
>
>

Attachment: [text/html]
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
Thread
Running Totals?Barry Newton3 Aug
  • Re: Running Totals?Peter Brawley3 Aug
    • Re: Running Totals?Barry Newton3 Aug
      • Re: Running Totals?Barry Newton5 Aug
  • Re: Running Totals?Brent Baisley3 Aug
Re: Running Totals?Barry Newton4 Aug