I have a table called orders
that looks like this:
+--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | memberid | int(11) | YES | | NULL | | | deliverydate | date | YES | | NULL | | +--------------+---------+------+-----+---------+-------+
And that contains the following data:
+------+----------+--------------+ | id | memberid | deliverydate | +------+----------+--------------+ | 1 | 991 | 2019-10-25 | | 2 | 991 | 2019-10-26 | | 3 | 992 | 2019-10-25 | | 4 | 992 | 2019-10-25 | | 5 | 993 | 2019-10-24 | | 7 | 994 | 2019-10-21 | | 6 | 994 | 2019-10-26 | | 8 | 995 | 2019-10-26 | +------+----------+--------------+
I would like a result set returning each unique date, and a separate column showing how many customers that placed their first order that day.
I’m having problems with querying this the right way, especially when the data consists of multiple orders the same day from the same customer.
My approach has been to
- Get all unique memberids that placed an order during the time period I want to look at
- Filter out the ones that placed their first order during the period by comparing the memberids that has placed an order before the timeperiod
- Grouping by delivery date, and counting all unique memberids (but this obviously counts unique memberids each day individually!)
Here’s the corresponding SQL:
SELECT deliverydate,COUNT(DISTINCT memberid) FROM orders WHERE MemberId IN (SELECT DISTINCT memberid FROM orders WHERE deliverydate BETWEEN '2019-10-25' AND '2019-10-26') AND NOT MemberId In (SELECT DISTINCT memberid FROM orders WHERE deliverydate < '2019-10-25') GROUP BY deliverydate ORDER BY deliverydate ASC;
But this results in the following with the above data:
+--------------+--------------------------+ | deliverydate | COUNT(DISTINCT memberid) | +--------------+--------------------------+ | 2019-10-25 | 2 | | 2019-10-26 | 2 | +--------------+--------------------------+
The count for 2019-10-26 should be 1.
Appreciate any help 🙂
Advertisement
Answer
You can aggregate twice:
select first_deliverydate, count(*) cnt from ( select min(deliverydate) first_deliverydate from orders group by memberid ) t group by first_deliverydate order by first_deliverydate
The subquery gives you the first order data of each member, then the outer query aggregates and counts by first order date.
This demo on DB Fiddle with your sample data returns:
first_deliverydate | cnt :----------------- | --: 2019-10-21 | 1 2019-10-24 | 1 2019-10-25 | 2 2019-10-26 | 1
In MySQL 8.0, This can also be achieved with window functions:
select deliverydate first_deliverydate, count(*) cnt from ( select deliverydate, row_number() over(partition by memberid order by deliverydate) rn from orders ) t where rn = 1 group by deliverydate order by deliverydate