CODEWITHSUNDEEP
mysqlsqldatabasegrouping
Le Ray
Le Ray

Reputation: 387

MySQL Grouping from a database

I am creating a database that will have occurance, and time of occurance date and hour/min/sec.

OCCURANCE   TIME
FAIL        01/01/2014 01:00:00
FAIL        01/01/2014 01:05:00
FAIL        01/01/2014 02:11:11
FAIL        01/01/2014 02:09:00
FAIL        01/01/2014 02:11:11

I am trying to find out how many occurances occured each hour within a given time and date range. I want to group them on an hourly basis.

select count(*) from table where TIME between '01-01-2014 01:00:00' AND '01-01-2014 03:00:00' GROUP BY(not sure)

Expected result 

Date                   count
01/01/2014 hour 1        2  
01/01/2014 hour 2        3

Should I only dump the hours in a column instead of minutes and seconds too? I think this might work, but I am no expert in this.

e.g 

OCCURANCE     DATE      HOUR
FAIL        01/01/2014    01
FAIL        01/01/2014    01
FAIL        01/01/2014    02
FAIL        01/01/2014    02
FAIL        01/01/2014    02

Upvotes: 0

Views: 46

Answers (1)

juergen d
juergen d

Reputation: 204854

select OCCURANCE, date(time), hour(time), count(*) 
from table 
where TIME between '01-01-2014 01:00:00' AND '01-01-2014 03:00:00' 
GROUP BY OCCURANCE, date(time), hour(time)

Upvotes: 2

Related Questions