tsql - t-sql count number of orders by each customer per month INCLUDING ZEROs

Question

Hi SQL experts appreciate any assistance on this. I have an orders table and a customer table linked by customerid and I'd like to do two things but can't work out how:

1. Show number of orders made by each customer per month, including when they made zero orders

2. Count the number of customers that have made certain count range of orders per month, e.g. 0, 1-10, 11-20 orders in each month etc.

It's something that has confused me, I'm a relative T-SQL novice.

question from:https://stackoverflow.com/questions/65829277/t-sql-count-number-of-orders-by-each-customer-per-month-including-zeros

Answer 1

You can use conditional aggregation and two levels of aggregation:

select yyyy, mm,
       count(*) as num_customers,
       sum(num_orders) as num_orders,
       sum(case when num_orders = 1 then 1 else 0 end) as cnt_1_order,
       sum(case when num_orders = 2 then 1 else 0 end) as cnt_2_order,
       sum(case when num_orders = 3 then 1 else 0 end) as cnt_3_order
from (select o.customer_id,
             year(o.order_date) as yyyy, month(o.order_date) as mm,
             count(*) as num_orders
      from orders o
      group by o.customer_id
     ) o
group by yyyy, mm
order by yyyy, mm;

You can modify the sum()s in the outer query to have the specific values or ranges that you care about.

If you want 0s, just subtract the total number of customers from the number that ordered in each month. That would be something like this:

select yyyy, mm,
       count(*) as num_customers_with_order,
       c.cnt - num_customers as customers_no_orders,
       sum(num_orders) as num_orders,
       sum(case when num_orders = 1 then 1 else 0 end) as cnt_1_order,
       sum(case when num_orders = 2 then 1 else 0 end) as cnt_2_order,
       sum(case when num_orders = 3 then 1 else 0 end) as cnt_3_order
from (select o.customer_id,
             year(o.order_date) as yyyy, month(o.order_date) as mm,
             count(*) as num_orders
      from orders o
      group by o.customer_id
     ) o cross join
     (select count(*) as cnt from customers) c
group by yyyy, mm
order by yyyy, mm;