1

enter image description hereenter image description hereI work in a parking company. We need a report of monthly vehicle exits by time. 

select hour(datetimeout) as 'dateout',count(*) 
from to_leaveveh 
where datetimeout between '2014-09-01' and '2014-09-02' 
group by dateout

This only shows data for one day. I want to create a monthly report with:

  1. Time in 24 hour format
  2. Count of vehicle exits
RANJIT KUMAR
  • 11
  • 3

2 Answers2

2

Use the DATE_FORMAT function

SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

or

SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

Give it a Try !!!

UPDATE 2014-09-22 08:26 EDT

The queries I gave break down each by our hour of the month. You get no more than 24 rows.

If you want group by date and hour, then try this

SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

or

SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

Give it a Try !!!

UPDATE 2014-09-22 15:07 EDT

This will group by date and hour with a summary by date and an overall summary

SELECT
    IF(
        ISNULL(dt)+ISNULL(hr)=2,'Monthly Total',
        IF(ISNULL(hr)=1,CONCAT('Total for ',dt),HOUR(hr))
    ) Statistic,VehicleExitCount
FROM
(
    SELECT
        DATE(datetimeout) dt,
        DATE_FORMAT(datetimeout,'%Y-%m-%d') hr,
        COUNT(*) VehicleExitCount
    FROM to_leaveveh
    GROUP BY dt,hr
    WITH ROLLUP
) A;

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Not working above query. – RANJIT KUMAR Sep 22 '14 at 10:07
  • Sorry, I had WHERE twice. I replaced the second WHERE with the word AND. Give it a Try !!! – RolandoMySQLDBA Sep 22 '14 at 11:18
  • @RolandoMySQLDBA, shouldn't that be < instead of <= and + 0 seconds, i.e.: AND datetimeout < '2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND ? – ypercubeᵀᴹ Sep 22 '14 at 11:20
  • @ypercube you're right. I tweeked it. Thanks for looking over my shoulder :-) – RolandoMySQLDBA Sep 22 '14 at 11:22
  • Thanks it is working, but i want to day shows in column format which is show above picture. – RANJIT KUMAR Sep 22 '14 at 17:17
  • @RolandoMySQLDBA,thanks for giving solution.Please help on create monthly report inwhich ROW show only date field and column show date(1-30). Report sample excel copy attached above. Kindly give me solution for the same. – RANJIT KUMAR Sep 22 '14 at 17:20
  • Sir, above query is running but not fullfil my requirement. it is showing output Statistic VehicleExitCount 0 1294 Total for 2014-06-23 1294 0 1373 Total for 2014-06-24 1373 0 1425 Total for 2014-06-25 1425 0 1508 Total for 2014-06-26 1508 0 1659 Total for 2014-06-27 1659 0 1289 Total for 2014-06-28 1289 0 1401 Total for 2014-06-29 1401 0 1453 Total for 2014-06-30 1453 But we want to create report in other form which is mention excel image. – RANJIT KUMAR Sep 23 '14 at 05:39
0

Try this

select hour(datetimeout) as 'dateout',count(*) 
from to_leaveveh 
where datetimeout between '2014-09-01' and '2014-09-02' 
group by hour(datetimeout);
vijayp
  • 2,766
  • 1
  • 16
  • 20