jyu1992
jyu1992

Reputation: 25

MYSQL - How to find which hour has the most number of searches

I'm having trouble with using datetime values. I want to find which hour has the most number of searches Here is some sample data

 Srch_id         dttime
  1             2013-04-04 08:32:15
  1             2013-04-04 08:32:15
  1             2013-04-04 08:32:15
  1             2013-04-04 08:32:15
  2             2012-12-31 08:59:22
  2             2012-12-31 08:59:22
  2             2012-12-31 08:59:22
  3             2013-12-31 09:22:22
  3             2013-12-31 09:22:22

This is the result I want

  Searches      HourofSearch
  7             08

select srch_id, (hour(dttime) from table... Not sure where to go from here

Upvotes: 0

Views: 32

Answers (1)

juergen d
juergen d

Reputation: 204884

Group by the hour, order by the count for each and take the first record only

select hour(dttime) as HourofSearch, count(*) as Searches
from your_table
group by hour(dttime)
order by count(*) desc
limit 1

Here is a working example.

Upvotes: 1

Related Questions