0

I am trying to count how many hours of the day there is data in the database.

I use this query:

SELECT
HOUR(date) AS `hour`, COUNT(date)
FROM fb_posts 
WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY
GROUP BY hour

I got the query from here

example:

hour           COUNT(date)
00             55
01             2
02             33

Now I want calculate how many hours there was data? Above example should output value 3, because there was data at hour 00, 01 and 02

something would like to add COUNT(hour)like this:

SELECT
HOUR(date) AS `hour`, COUNT(date)**,COUNT(hour)**
FROM fb_posts 
WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY
GROUP BY hour
Community
  • 1
  • 1
Daniel
  • 3
  • 4

2 Answers2

1
select count(*) 
from
(
  SELECT HOUR(date) AS hour
  FROM fb_posts 
  WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY
  GROUP BY hour
) tmp
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

you are looking for count(distinct column) https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count-distinct

  SELECT count(distinct HOUR(date)) AS distinct_hour_count
  FROM fb_posts 
  WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY
AdrianBR
  • 2,762
  • 1
  • 15
  • 29