CODEWITHSUNDEEP
mysqldatecountwhere-clausehour
Daniel
Daniel

Reputation: 3

MYSQL how to count how many hours where data exists

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

Upvotes: 0

Views: 72

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

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

Upvotes: 1

juergen d
juergen d

Reputation: 204854

select count(*) 
from
(
  SELECT HOUR(date) AS hour
  FROM fb_posts 
  WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY
  GROUP BY hour
) tmp

Upvotes: 1

Related Questions