Hourly based report in sql query

Get a hourly based report for count the number of vehicles traveling on that particular hour(0-23 hour) in MYSQL .
If there is no vehicles traveled on that hour,supposed to be displayed 0 , otherwise it will give count the no of vehicles travelled.

MySQL Query is displayed only the vehicles entered hour only
        SELECT Extract(hour from payment_date_time)Hour,Count(amount)Volume,sum(amount)Amount from carpark_db.payment where carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment_date_time >= '2018-01-02 00:00:00' and payment_date_time < '2018-01-02 23:59:59' group by extract(hour from payment_date_time) order by extract(hour from payment_date_time

plz help me
jakkku maAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
You might need to create a temporary table to achieve this.. used MySQL long time back, so slightly check the syntax once..
CREATE TEMPORARY TABLE t_hour
SELECT 1 as hr
UNION ALL
SELECT 2 as hr
UNION ALL
SELECT 3 as hr
UNION ALL
SELECT 4 as hr
UNION ALL
SELECT 5 as hr
UNION ALL
SELECT 6 as hr
UNION ALL
SELECT 7 as hr
UNION ALL
SELECT 8 as hr
UNION ALL
SELECT 9 as hr
UNION ALL
SELECT 10 as hr
UNION ALL
SELECT 11 as hr
UNION ALL
SELECT 12 as hr
UNION ALL
SELECT 13 as hr
UNION ALL
SELECT 14 as hr
UNION ALL
SELECT 15 as hr
UNION ALL
SELECT 16 as hr
UNION ALL
SELECT 17 as hr
UNION ALL
SELECT 18 as hr
UNION ALL
SELECT 19 as hr
UNION ALL
SELECT 20 as hr
UNION ALL
SELECT 21 as hr
UNION ALL
SELECT 22 as hr
UNION ALL
SELECT 23 as hr
UNION ALL
SELECT 24 as hr



SELECT t_hour.hr Hour,Count(amount)Volume,sum(amount)Amount 
from t_hour
LEFT JOIN carpark_db.payment on t_hour.hr = Extract(carpark_db.payment.hour from payment_date_time)
where carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment_date_time >= '2018-01-02 00:00:00' 
and payment_date_time < '2018-01-02 23:59:59' 
group by t_hour.hr
order by t_hour.hr

Open in new window

0
jakkku maAuthor Commented:
SELECT t_hour.hr Hour,Count(payment.amount)Volume,sum(payment.amount)Amount
from carpark_db.t_hour
LEFT JOIN carpark_db.payment on t_hour.hr = Extract(hour from payment.payment_date_time)
where payment.carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment.payment_date_time >= '2018-01-02 00:00:00'
and payment.payment_date_time < '2018-01-02 23:59:59'
group by t_hour.hr
order by t_hour.hr
getting.PNG
still getting the same result , i want to display 0 in the volume and amount column if vehicles not entered into the particular hour
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Kindly confirm whether the result starts only from hour 6 or you have any entries before that so that we can check accordingly..
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

jakkku maAuthor Commented:
SELECT t_hour.hr Hour,Count(*)Volume,sum(payment.amount)Amount
from (SELECT 0 AS hr
         UNION ALL SELECT 1 AS hr
         UNION ALL SELECT 2 AS hr
         UNION ALL SELECT 3 AS hr
         UNION ALL SELECT 4 AS hr
         UNION ALL SELECT 5 AS hr
         UNION ALL SELECT 6 AS hr
         UNION ALL SELECT 7 AS hr
         UNION ALL SELECT 8 AS hr
         UNION ALL SELECT 9 AS hr
         UNION ALL SELECT 10 AS hr
         UNION ALL SELECT 11 AS hr
         UNION ALL SELECT 12 AS hr
         UNION ALL SELECT 13 AS hr
         UNION ALL SELECT 14 AS hr
         UNION ALL SELECT 15 AS hr
         UNION ALL SELECT 16 AS hr
         UNION ALL SELECT 17 AS hr
         UNION ALL SELECT 18 AS hr
         UNION ALL SELECT 19 AS hr
         UNION ALL SELECT 20 AS hr
         UNION ALL SELECT 21 AS hr
         UNION ALL SELECT 22 AS hr
         UNION ALL SELECT 23 )AS t_hour LEFT JOIN carpark_db.payment on t_hour.hr = Extract(hour from payment.payment_date_time)
where payment.carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment.payment_date_time >= '2018-01-02 00:00:00'
and payment.payment_date_time < '2018-01-02 23:59:59' group by t_hour.hr order by t_hour.hr


this fetch only those record for which i have vehicles .rest of time interval should show me count 0 but its not showing 0.please help me to solve.trying for more than week
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Got it, try this..
SELECT t_hour.hr Hour,Count(*)Volume,sum(payment.amount)Amount
from (SELECT 0 AS hr
         UNION ALL SELECT 1 AS hr
         UNION ALL SELECT 2 AS hr
         UNION ALL SELECT 3 AS hr
         UNION ALL SELECT 4 AS hr
         UNION ALL SELECT 5 AS hr
         UNION ALL SELECT 6 AS hr
         UNION ALL SELECT 7 AS hr
         UNION ALL SELECT 8 AS hr
         UNION ALL SELECT 9 AS hr
         UNION ALL SELECT 10 AS hr
         UNION ALL SELECT 11 AS hr
         UNION ALL SELECT 12 AS hr
         UNION ALL SELECT 13 AS hr
         UNION ALL SELECT 14 AS hr
         UNION ALL SELECT 15 AS hr
         UNION ALL SELECT 16 AS hr
         UNION ALL SELECT 17 AS hr
         UNION ALL SELECT 18 AS hr
         UNION ALL SELECT 19 AS hr
         UNION ALL SELECT 20 AS hr
         UNION ALL SELECT 21 AS hr
         UNION ALL SELECT 22 AS hr
         UNION ALL SELECT 23 )AS t_hour 
LEFT JOIN carpark_db.payment on t_hour.hr = Extract(hour from payment.payment_date_time)
AND payment.carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment.payment_date_time >= '2018-01-02 00:00:00'
and payment.payment_date_time < '2018-01-02 23:59:59' 
group by t_hour.hr order by t_hour.hr

Open in new window

0
jakkku maAuthor Commented:
Thank you sir,I got the output but amount show NULL instead of 0
out.PNG.



 Please tell me how to fast retrieve data from database and displays it in web page.If monthly or yearly report takes time to retrieve data from DB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Okay, change the first line to this and if required add COALESCE to Volume as well..
SELECT t_hour.hr Hour,Count(*)Volume,COALESCE(sum(payment.amount),0) Amount

Open in new window

2
jakkku maAuthor Commented:
Please tell me how to fast retrieve data from database and displays it in web page.If monthly or yearly report takes time to retrieve data from DB
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Please tell me how to fast retrieve data from database and displays it in web page.

If you want to retrieve lot of data, then first try to see whether the query can be tuned to retrieve the data faster.
If the query is optimized and not helping, then either try to increase the timeout in web page so that it can render and display all data or restrict the users to run report for lesser duration so that web page returns data faster. Or else try to see whether you can export the data to an excel or text file if rendering takes more time..
0
jakkku maAuthor Commented:
I got the Output sir.Thank u so much
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.