CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 1

Mysql hourly report query

I want to get an hourly report of conversions for all 24 hours. I have this query but it returns only 19rows instead of 24 can anyone plz tell me wats wrong in this? Thanks in advance.

SELECT   HOUR( `date_time` ) AS Hours, COUNT(conversion_id) AS `conversion` FROM conversions 
RIGHT JOIN (SELECT  0 AS Hour UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT  
    5 UNION ALL SELECT  6  UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11 
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL 
    SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) AS AllHours 
ON HOUR(date_time) = Hour 
WHERE DATE(date_time) = CURDATE() OR date_time IS NULL 
GROUP BY Hour 
ORDER BY Hour

Upvotes: 0

Views: 1027

Answers (2)

bernhardrusch
bernhardrusch

Reputation: 11890

If there are not entries for this hour, it is never selected. You have to query the other way round.

I think it should be something like this (hard to test without your database):

select * from (SELECT  0 AS Hour UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT       5 UNION ALL SELECT  6  UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11      UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL      SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23)   as AllHours
left outer join 
    (select  COUNT(conversion_id) as cnt, HOUR(date_time) as h 
     FROM conversions 
     WHERE DATE(date_time) = CURDATE() OR date_time IS NULL 
     group by h) as c 
on Hour = e.h

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The right join is almost correct. I prefer that the where condition be in the on clause (rather than checking for NULL values. The key, though, is using the AllHours table in the select and group by:

SELECT   AllHours.Hour AS Hours, COUNT(conversion_id) AS `conversion`
FROM conversions RIGHT JOIN
      (SELECT  0 AS Hour UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT  
    5 UNION ALL SELECT  6  UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11 
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL 
    SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
      ) AS AllHours 
      ON HOUR(conversions.date_time) = AllHours.Hour and DATE(conversions.date_time) = CURDATE()
GROUP BY AllHOurs.Hour 
ORDER BY AllHours.Hour

Upvotes: 0

Related Questions