MySQL Query to get each sales per month-CodePudding

I have 2 tables in Mysql. I want to regroup and count the Number of Orderid per month for each customer. If there is no order, I would like to add 0.

Customer Table

CustomerID              
    1
    2
    3

Order Table

OrderId  CustomerID      Date
1          1           2022-01-02
2          1           2022-01-04
3          2           2022-02-03
4          2           2022-03-03

Expect results

 CustomerID       Date       CountOrderID
    1            2022-01         2 
    2            2022-01         1
    3            2022-01         0
    1            2022-02         0 
    2            2022-02         1
    3            2022-02         0
    1            2022-03         0 
    2            2022-03         1
    3            2022-03         0

How I can do this in Mysql?

CodePudding user response：

SELECT customer.CustomerID,
       year_month.y_m AS `Date`,
       COUNT(order.OrderId) AS CountOrderID
FROM customer
CROSS JOIN (
    SELECT DISTINCT DATE_FORMAT(`date`, '%Y-%m') AS y_m
    FROM order 
    ) AS year_month
LEFT JOIN order ON order.CustomerID = customer.CustomerID
               AND DATE_FORMAT(order.`date`, '%Y-%m') = year_month.y_m
GROUP BY 1, 2;

If order table does not contains for some year and month then according row won't present in the output. If you need in it then you'd generate calendar table instead of year_month subquery.

CodePudding user response：

you can reduce the number of cte's I added more here to explain the steps:

first you need the format year and month, for that I used DATE_FORMAT() function
since you need to have all the combination of dates and the year month you need a cross join. This will produce all the distinct dates with all the distinct customer id's. In other words all the pairs between dates and customer id
once you have a table with all the combinations you need to pass the actual data with the left join this will produce null where you actually don't have rows and hence will produce 0 when the count is performed
the last step is simply count function

with main as (
    
    select distinct DATE_FORMAT(date,'%Y-%m') as year_month from order
    ),
    
    calendar as (
    
    select * from customer
    cross join main
    ),
    joining_all as (
    
    select 
    calendar.*,
    order. OrderId
    left join order
    on calendar.CustomerID = order.CustomerID
    and calendar.year_month = DATE_FORMAT(order.date,'%Y-%m')
    )
    
    select 
    CustomerID,
    year_month as Date,
    count(OrderId) as CountOrderID
    from joining_all
    group by 1,2

maybe the shorter version can work with the code below. if runs into syntax you can use the one above

with main as (
select distinct DATE_FORMAT(date,'%Y-%m') as year_month from order
cross join customer
)
 select 
    main.CustomerID,
    main.year_month as Date,
    count(order.OrderId) as CountOrderID
    from main
    left join order
    on main.CustomerID = order.CustomerID
    and main.year_month = DATE_FORMAT(order.date,'%Y-%m')
group by 1,2