0

I am trying to group the last 24 Hours of analytics by hour

this is my table.

enter image description here

I can run this query and it will group with a total.

SELECT SUM( watched_time ) AS total
FROM wp_video_analytics_time 
WHERE created > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(created)

It outputs.

enter image description here

What I am trying to achieve is to output the date alongside the grouped column.

Something like this.

enter image description here

I am trying to do this so I can add this data to a Google chart and display the time watched in the last 24 hours.

Ideally I would like the whole 24 hours with zero values if there is no data. But any help would be appreciated at this stage.

Ok so I have the hours working.

SELECT SUM( watched_time ) AS total, HOUR(created) AS hours
FROM wp_video_analytics_time 
WHERE created > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(created)

Can anyone explain how to add the zero values for hours that dont have any data this does not seem simple.

user1503606
  • 3,872
  • 13
  • 44
  • 78
  • See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Mar 23 '21 at 13:28
  • *how to add the zero values for hours that dont have any data* Use generated hours table (statically or in CTE) as a base datasource. – Akina Mar 23 '21 at 13:39

0 Answers0