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.
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 .
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