Question 112886 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query to get each sales per month

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search