Posted by ZeldorBlat on 02/26/07 22:03

On Feb 26, 3:19 pm, "Bob Bedford" <bedf...@nospam.com> wrote:
> Hi all,
>
> I've a huge number of records in one table and must retrieve them.
>
> Every record has a datetime field and other values (mainly numerical
> fields).
>
> My goal is to retrieve min,max,average for every month. In fact I've to
> create charts and I can't do that using every day. I've also to know the %
> of the record for every month given the entire number of record.
> I can get those values then use them in an other pogram to create charts but
> I don't know how to retrieve the datas grouping by month.
> In pseudo-code:
> select min(x), max(x), average(x), numberofrecorpermonth, totalnumber from
> mytable group by everymonth
> table mytable:
> x: integer;
> logtime: DateTime
>
> - numberofrecordpermonth is the count of records for every month
> - totalnumber is the total count of records
>
> How to do it ? I have not enough knowledge in sql to do it.
>
> Thanks for helping.
>
> Bob

I'll assume you're using MySQL. MONTH() will return the month of a
datetime value, and YEAR() will return the year of a datetime value.
Basically you want to group on those columns. So something like this:

select year(logtime) theYear, month(logtime) theMonth,
min(x), max(x), average(x), count(*)
from mytable
group by year(logtime), month(logtime)

[Back to original message]