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
1
  • Can you add some sample output, how it will look like
    – vijayp
    Sep 20 '14 at 5:59
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 !!!

7
  • Not working above query. Sep 22 '14 at 10:07
  • Sorry, I had WHERE twice. I replaced the second WHERE with the word AND. Give it a Try !!! 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 ? Sep 22 '14 at 11:20
  • @ypercube you're right. I tweeked it. Thanks for looking over my shoulder :-) Sep 22 '14 at 11:22
  • Thanks it is working, but i want to day shows in column format which is show above picture. Sep 22 '14 at 17:17
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);
3
  • Sir, it showing datepart is not register function. i want to create report inwhich row showed Time in 24format and column showed 1st to 30th all vehicle out data. Sep 21 '14 at 2:32
  • DATEPART is not register function as showing query error message output. Sep 21 '14 at 2:38
  • 1
    @vijayp the OP is getting notifications when you edit your answer, no need to ping him with comments. Sep 22 '14 at 11:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.