statistical queries

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

Re: statistical queries

On Feb 26, 3:19 pm, "Bob Bedford" 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)