How to find count for every hour in mysql

Hi,

I want to write a query where in i have to show count of data entered by every hour .

i.e
hours
(from midnight to mid-night)

00-01 : how many counts
01-02 : ‘’
02-03 : "

:
:
:
22-23:"
23-24:"

any one know how to find i did a trick but i have to fire 24 queries for it specific for hour interval.

Can a single query do this job - group by,having ???

Thanks


select extract(hour from yourDateColumn) as theHour,
         count(*) as numberOfItems
  from yourTable
 group by extract(hour from yourDateColumn)

00-01 : how many counts
01-02 : ‘’
02-03 : "

:
:
:
22-23:"
23-24:"

this is “Daily” kinds

yourDateColumn - there is no field Every day has 24 hours and i want to check what is the lead count between evry hours starting from mid-night.

I hope i make some sense !!

Thanks

No, it does not make sense. Give some sample data and the expected result.

priti, swampBoogie’s solution looks good to me.
If you don’t understand how it works, and can’t adapt it to fit your situation, then please post your table structure, some example data, and the query you got so far.

consider

submitted transaction_k

2009-01-27 00:00:12 abc1233
2009-01-27 01:00:12 abc1234
2009-01-27 02:00:12 abc1234
2009-01-27 03:00:12 abc1294
2009-01-27 04:00:12 abc1234
2009-01-27 04:40:12 abc1234
2009-01-27 05:00:12 abc1453
2009-01-27 06:00:12 abc1232
2009-01-27 07:00:12 abc1634
2009-01-27 07:00:12 abc1736

now
hour-count
00-01 - 1
01-02 - 1
02-03-1
03-04 -1
04-05 - 2 count

start hour-end hour
00-01
01-02
03-04
05-06

;

;

likewise i will have 24 entries in my table.It’s a cron job so at mid night it will start and count how many (transaction_k) count are received in particular time range .

Now it will be difficult to be more precise then this .:slight_smile:

So… what is wrong with the query swampBoogie posted?

That query is gr8 and work if no specific date is given.

SELECT extract( HOUR
FROM submitted ) AS theHour, count( * ) AS numberOfItems
FROM table
WHERE date_format( submitted , ‘%d-%m-%Y’ ) = ‘24-01-2009’
GROUP BY extract( HOUR
FROM submitted )

this fetch only those record for which i have transaction rest of time interval sud show me count 0.

SELECT h.theHour
     , COUNT(submitted) AS numberOfItems
  FROM ( SELECT 0 AS theHour
         UNION ALL SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
         UNION ALL SELECT 6
         UNION ALL SELECT 7
         UNION ALL SELECT 8
         UNION ALL SELECT 9
         UNION ALL SELECT 10
         UNION ALL SELECT 11 ) AS h
LEFT OUTER
  JOIN table
    ON EXTRACT(HOUR FROM table.submitted) = h.theHour
   AND DATE(table.submitted) = '2009-01-24'
GROUP
    BY h.theHour