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

Mysql – count the number of customers who ordered twice for each month

i have table like this (each row represents an order):

order_id   date                    customer_id 
11        '2023-01-01 00:03:43'    123
12        '2023-02-01 00:02:43'    456
13        '2023-02-01 00:03:44'    789
14        '2023-04-01 00:03:43'    1230

i tried this:

SELECT DATE_FORMAT(date,'%Y-%m') , COUNT(distinct customer_id) as count
FROM `order` o
GROUP BY DATE_FORMAT(o.date,'%Y-%m') 
HAVING COUNT(o.customer_id) > 1

It works but also counts customers from previous months. For example, a customer counted in January should not be counted in February. How can I do that ?

Tags:

3

Answers


  1. 0

    try this query. Return the result of all distinct customer counts per month and year-wise.

    SELECT 
    YEAR(o.date) AS year,
    MONTH(o.date) AS month,
    COUNT(distinct customer_id) AS count
FROM 
    order o
GROUP BY 
    YEAR(o.date),
    MONTH(o.date)
ORDER BY 
    YEAR(o.date) ASC,
    MONTH(o.date) ASC;
    Login or Signup to reply.
  2. 0

    It works but also counts customers from previous months. For example,
    a customer counted in January should not be counted in February. How
    can I do that ?

    To ensure that each customer is counted just once, we will obtain the earliest date possible for each customer using groub by and min():

    select customer_id, min(date) as min_date
from mytable
group by customer_id

    Then apply your logic using group by and having on this dataset :

    SELECT DATE_FORMAT(min_date,'%Y-%m') , COUNT(customer_id) as count
FROM (
  select customer_id, min(date) as min_date
  from mytable
  group by customer_id
) o
GROUP BY DATE_FORMAT(o.min_date,'%Y-%m') 
HAVING COUNT(o.customer_id) > 1;

    Demo here

    Login or Signup to reply.
  3. 0

    You can first find out which customers have more exactly two orders per month:

    select customer_id, DATE_FORMAT(date,'%Y-%m') as 'month'
from `order` o
group by customer_id, DATE_FORMAT(date,'%Y-%m')
having count(*) = 2

    Now that you have that list, you can calculate number of customers per month:

    select `month`, count(customer_id) 'Customer_cnt'
from (
  select customer_id, DATE_FORMAT(date,'%Y-%m') as 'month'
  from `order` o
  group by customer_id, DATE_FORMAT(date,'%Y-%m')
  having count(*) = 2
) as q
group by `month`

    See db-fiddle

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

Back To Top
Search