Counting Rows And Grouping By Day 1

[Rich20B]

Hi guys,

This problem has been puzzling me for a couple of days now, hopefully you can help.

I have a table called 'msgs' which stores the text of log entries, together with a timestamp of when the message was generated:

CREATE TABLE `msgs` (
`time_num` int(10) unsigned NOT NULL default '0',
`message` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to be able to see how many log entries were generated on particular days of the week for the past seven days:

Mon 2
Tue 33
Wed 99
Thu 0
Fri 97
Sat 99
Sun 3

The following query works, though only returns results for days when there were entries logged:

Tue 3
Wed 33

SELECT COUNT(*) AS num, DATE_FORMAT(FROM_UNIXTIME(time_num), '%a') as tday
FROM msgs
WHERE time_num BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AND NOW()
GROUP BY tday

Any ideas how to make this query return NULL (or '0') in the case that there weren't any log entries on a particular day - so that we always get 7 days of results regardless.

Usually you'd use a left join which would return all rows - though in this case there's no other table to left join with.

I've tried creating a table called 'days' with a single field called 'day' pre-populated with the days of the week, then using LEFT JOIN days ON days.day = DATE_FORMAT(FROM_UNIXTIME(time_num), '%a') though this also returns only days where entries were logged.

Thanks in advance

 

[r937]

... though in this case there's no other table to left join with

luckily, since there are only 7 days in a week, it is easy to generate them "on the fly"

SELECT COUNT(msgs.time_num) AS num
     , dt.tday
  FROM (
       SELECT 1 AS tday UNION ALL
       SELECT 2 UNION ALL
       SELECT 3 UNION ALL
       SELECT 4 UNION ALL
       SELECT 5 UNION ALL
       SELECT 6 UNION ALL
       SELECT 7
       ) AS dt
LEFT OUTER
  JOIN msgs
    ON DAYOFWEEK(FROM_UNIXTIME(msgs.time_num)) = dt.tday
   AND time_num 
       BETWEEN UNIX_TIMESTAMP(
                   DATE_SUB(CURDATE(), INTERVAL 7 DAY) ) 
           AND UNIX_TIMESTAMP( NOW() )
GROUP 
    BY dt.tday

r937.com | rudy.ca