3

I'm afraid this is probably a very embarrassingly easy question - but my mind is just completely stuck at this hour.

I have a table that stores the number of activities carried out by different people, and the time it took place in.

I want to create a report that accepts the person's name as a parameter, and show the number of activities per hour for that person during each of the previous 24 hours starting from current timestamp (now()).

Right now,

SELECT hour(TimeStamp), activities FROM tbl1 
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR)  AND Name = ? 
GROUP BY hour(timestamp)

only returns to me those hours when any activity was present. However, I want a complete 24 hour breakdown with zero for when there was no activity.

i.e. I'm getting

Hour | No. of Activities
1    | 34
4    | 22
9    | 86

but I want

Hour | No. of Activities
1    | 34
2    | 0
3    | 0
4    | 22
5    | 0
... etc.

How do I do this?

(The order of hours in the example is irrelevant)

colonel_px
  • 305
  • 4
  • 16

1 Answers1

6

You can create a hourly table, and do a LEFT JOIN

create table hourly
(
  /* hour is not a reserved keyword */
  hour smallint(2) not null default 0
);

insert into hourly values (0),(1).... until 24

SELECT 
  hourly.hour, 
  COALESCE(COUNT(activities),0) AS "No of Activities"
FROM hourly
LEFT JOIN tbl1 ON hourly.hour=hour(tbl1.TimeStamp)
WHERE 
  tbl1.timestamp>=DATE_SUB(NOW(), INTERVAL 24 HOUR) AND 
  tbl1.Name=? 
GROUP BY hourly.hour
ORDER BY hourly.hour;
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • @ajreal Sorry, but I can't get the COALESCE function to return a Zero for some reason. `SELECT hourly.hour, COALESCE(activities, 0) FROM hourly LEFT JOIN tbl1 ON hourly.hour=hour(Timestamp) WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR) AND Name = ? GROUP BY hour(timestamp) ORDER BY hourly.hour ` Still returns only the hours in which activities are present. – colonel_px Sep 10 '11 at 22:07
  • How about replace the GROUP BY to hourly.hour? – ajreal Sep 10 '11 at 22:11
  • @ajreal Tried both. Neither is working. I must mention I am summing the activities, but trying without the sum doesn't work either. Am using MySQL 5.5.8 What could be going wrong? – colonel_px Sep 10 '11 at 22:26
  • Updated again, and next time please include more details. It does not work don't really helping... – ajreal Sep 10 '11 at 22:31
  • @ajreal I mean, I have grouped by hourly.hour as well. It still does not return me any rows for hours in which activity was not present. I have also tried IFNULL instead of COALESCE with similar results. – colonel_px Sep 10 '11 at 22:37
  • ,sorrymw brain is dead at te moment, you can try to join the hourly table with result of group by from your table . – ajreal Sep 10 '11 at 22:45
  • 1
    which mean hourly left join (select group your table) – ajreal Sep 10 '11 at 22:47
  • having another look, the current one should be correct.because is last 24 hour can check any row is matched ? – ajreal Sep 10 '11 at 22:52
  • @ajreal Thanks, you pointed me in the right direction this time. Had to join it with the grouped query. – colonel_px Sep 10 '11 at 23:09
  • @ajreal I've edited your answer. But it needs to be peer reviewed. – colonel_px Sep 11 '11 at 07:59
  • @colonel_px - I know it's been a while since this question was posted, but how did you get this to work? I'm having exactly the same issue, but the above solution isn't returning zero results. – aphextwix Dec 01 '14 at 16:21