group by date and day

[jansky]

to all guru
i have my table with a field date_ref (date type) and also stat (integer)

1. how do i group by month? example July 2005 total stat =22
   then i will get the total stat
2. how do i group by day ?example july 18,2005 total stat=5
   then i will get the total stat also

thanks

[nemonoman]

Use date formats:
SELECT SUM(stat), COUNT(stat), DATE_FORMAT(date_ref, '%Y%m')
FROM YourTableNameHere
GROUP BY DATE_FORMAT(date_ref, '%Y%m')

That will get you months: I leave you to search to the mysql manual date_format section to figure out how to get totals by day.

[lajkonik86]

Select date_ref, count(stat) as total_stat
From whatever
Group by distinct date_ref

my guess or something like that for the day atleast

For the month use some sort of function to group by only the month digit of the string

[lajkonik86]

Sum instead of count forget the distinct offcourse

[jansky]

to : nemonoman
thanks that really help and for the stat per day

select SUM( hit )totalhits,date_ref from stat where DATE_FORMAT( date_ref, '%Y%m' )=200505 group by DATE_FORMAT( date_ref, '%Y%m%d' )order by date_ref

this works perfect, You are my savior