How to get time wise sales report in mysql

snowal raj

9y933Oracle

hai,

I need Time Wise Sales Report in mysql,I get the Report like ,

Time Amount

19:00:00 2

20:00:00 3

21:00:00 0

22:00:00 0

23:00:00 9

but I need sum the Amount each time like

Time Amount

19:00:00 2

20:00:00 5

21:00:00 5

22:00:00 5

23:00:00 14

This is My Query

SELECT concat(MID(date_of_call, 12, 2),':00:00') TimeIntervel,COUNT(*) TotalEntries,date_format(date_of_call,'%d/%m/%Y') FROM ICC_MM_setup_suply
WHERE DATE_FORMAT(date_of_call,'%d/%m/%Y')='31/01/2017'
GROUP BY concat(MID(date_of_call, 12, 2),':00:00')

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

snowal raj9y ago
i have no error.This query result is first table,i need query or idea for second table,
Kindly give a query in mysql or mssql .
data type of date_of_call is datetime.

records of table ICC_MM_setup_suply is Attached
Nitin Sontakke9y ago
I don't think you can have third column in your query.

SELECT concat(MID(date_of_call, 12, 2),':00:00') TimeIntervel
,COUNT(*) TotalEntries
--,date_format(date_of_call,'%d/%m/%Y')
FROM ICC_MM_setup_suply
WHERE DATE_FORMAT(date_of_call,'%d/%m/%Y')='31/01/2017'
GROUP BY concat(MID(date_of_call, 12, 2),':00:00')
Nitin Sontakke9y ago
Unfortunately, I don't know MySQL, but what is the error? What is the data type of your date_of_call?