CODEWITHSUNDEEP
mysql
Eric Lee
Eric Lee

Reputation: 710

How to get order records whose user id already made more than 1 orders

Hi I'm trying to count order records with users who made more than one order in a month like 2018-01-01 to 2018-02-01.

Order table
id   user_id   date
1    12        2017-01-02  <- first order(no count)
2    23        2018-01-03  <- second order(count)
3    12        2018-01-04  <- second order(count)
4    12        2018-01-08  <- third order(count)
5    23        2017-11-02  <- first order(no count)
6    11        2018-01-01  <- first order(no count)
....

User table
id
11
12
23
....

Output

 date     count(*)
 2018-01-01     3

I think I need to get order records first and find order records again with certain user_id. But I'm stuck Is there a way to accomplish this task? Thanks

Upvotes: 0

Views: 636

Answers (1)

CLAbeel
CLAbeel

Reputation: 1088

How about this:

select count(*) from 
(select user_id, count(*)
from `Order`
where date >= '2018-01-01' and date < '2018-02-01'
group by user_id
having count(*) > 1) users_w_multiple_orders;

The having command is how you filter results from an aggregation like sum. After you have that, you can count the results from that query.

Upvotes: 1

Related Questions