Page 1 of 1

GROUP BY each day of week, even if there is no data for a da

Posted: Fri Jan 30, 2009 7:49 am
by mightymouse
Hello,

I am making a stats program and have run into a slight problem. I'm not even sure if MySQL can do this natively or whether I will need to use some PHP as well.

I have a database with each hit the site has and would like to be able to display graphs for the day/week/month/etc. However, when there are no hits for the day MySQL doesn't return a row for that day (which of course it shouldn't do). Is there any way which I can data for each day, regardless of whether there were any hits or not? The SQL I have at the moment is:

Code: Select all

SELECT FROM_UNIXTIME(date, '%Y-%m-%d') AS `group_date`,
COUNT(*) as 'hits'
FROM `hits`
GROUP BY `group_date`
ORDER BY `group_date` DESC
LIMIT 7

Re: GROUP BY each day of week, even if there is no data for a da

Posted: Fri Jan 30, 2009 12:50 pm
by Christopher
You could probably deal with there being no data elsewhere and not with the SELECT statement.

Re: GROUP BY each day of week, even if there is no data for a da

Posted: Fri Jan 30, 2009 5:45 pm
by VladSun
I'm almost sure it can not be done in native MySQL (at least - it will be not easy).
There is a very helpful function in PostgreSQL : http://www.postgresql.org/docs/8.0/stat ... s-srf.html
Maybe someone has written a similar function for MySQL.