List:General Discussion« Previous MessageNext Message »
From:mos Date:February 7 2004 4:28am
Subject:Re: iterating through a month
View as plain text  
At 05:38 PM 2/6/2004, you wrote:
> >In MySql is it possible to repeat through a month, for example I would like
> >to
>
> >Select count(user) from table where date = x
>
> >However, I want x to be a range from the first of the month to the last day
> >in the month, I of course would need to pass in a month and year value to
> >limit it to that month and year.
>
> >Basically, I am trying to provide a month at a glance report of total posts
> >by various users to a table.
>
>Looking for ... WHERE MONTH( date ) = x (x=1, 2, ... 12)?
>
>PB

You can try:

Select count(user) from table where date >= '2004-02-01' and date < 
'2004-03-01'

This is the fastest if date is an indexed column and because the last 
comparison operator is "<" and not "<=" you don't need to worry about the # 
of days in the month.  Clever, eh?<g>

If you want to summarize everyone by month you can also do a "group by" as in:

select  Cust, count(Cust) as Count, Year(date) as Year,  Month(date) as 
Month from table group by Cust, Year, Month

Mike 

Thread
A challenge I thinkJohn Berman6 Feb
  • Re: A challenge I thinkMartijn Tonies6 Feb
    • iterating through a monthScott Haneda6 Feb
      • Re: iterating through a monthPeter Brawley7 Feb
        • Re: iterating through a monthmos7 Feb
      • AW: iterating through a monthFreddie Sorensen7 Feb
    • RE: A challenge I thinkJohn Berman7 Feb
RE: A challenge I thinkWalt Weaver6 Feb
Re: iterating through a monthmos10 Feb