Hourly wise sales report-CodePudding

I have the data Like

Sales   Order_date
27  2022-03-29 7:27:12
22  2022-03-29 7:27:22
20  2022-03-29 7:27:28
15  2022-03-29 7:27:59
29  2022-03-29 7:33:31
18  2022-03-29 7:46:17
10  2022-03-29 7:48:40
21  2022-03-29 8:09:25
24  2022-03-29 8:15:58
18  2022-03-29 8:16:36     etc...

Can I get the o/p like below mentioned hourly based total_sales.

Ordr_date   Time     Total_Sales
2022-03-29  7:00 AM       141
2022-03-29  8:00 AM       63

Thanking you.

CodePudding user response：

here you go, I have taken the table name as Sales_Table so change it according to your needs,

select table1.`Time`
  ,max(table1.date_order) 
  ,sum(table1.sales) 
from (select *
         ,date(order_date) as date_order
         ,CONCAT(hour(order_date),':00') as `Time` 
  from Sales_Table
as table1 
group by table1.`Time`;

CodePudding user response：

Try:

select date(order_date) as date,
       hour(order_date) as hour ,
       sum(sales) as total_sales
from test_tbl
group by date,hour;

Demo

MySQL function:

Time

Hour

CodePudding user response：

Try: Mysql Query

select date(order_date) as date,DATE_FORMAT(order_date, '%I:%i %p') as Time ,sum(sales) as total_sales
from test_tbl
group by date,hour;