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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mightymouse
Forum Newbie
Posts: 4
Joined: Fri Dec 05, 2008 11:52 am

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

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

You could probably deal with there being no data elsewhere and not with the SELECT statement.
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply