In MySQL, return records with specific counts by year

user20015767 0
Viewed 44

I have an orders data set. I'd like to get email addresses where the count of orders are specific counts for each year. Let's say 2000 = 1, 2001 = 5 or less, 2002 = 3.

select email
from orders
where year in (2000,2001,2002)

That's where I'm stuck. My thought process is pushing me towards using a having clause or a case statement, but I'm at a wall with the condition of considering the counts by year.

In pseudo SQL it'd be:

select email
from orders
where count(year = 2000) = 1
and count(year = 2001) <= 5
and count(year = 2002) = 3
mysql sql
2 Answers

You can't do this in the where clause, you have to group by email and apply your condition in a having clause (or have your group by query as a subquery and use a where condition in an outer query).

select email
from orders
where year in (2000,2001,2003)
group by email
having sum(year = 2000) = 1
and sum(year = 2001) <= 5
and sum(year = 2002) = 3
ysth0

You can do it as bellow. Note that you can change the filtred values wthin the where condition for the count value and the associated year.

-- create a table
CREATE TABLE Orders (
  id INTEGER PRIMARY KEY,
  email VARCHAR(30) NOT NULL,
  year int NOT NULL
);
-- insert some values
INSERT INTO Orders VALUES (1, 'test1@mail.com', 2000);
INSERT INTO Orders VALUES (2, 'test2@mail.com', 2001);
INSERT INTO Orders VALUES (3, 'test2@mail.com', 2001);
INSERT INTO Orders VALUES (4, 'test3@mail.com', 2002);
INSERT INTO Orders VALUES (5, 'test2@mail.com', 2001);
INSERT INTO Orders VALUES (6, 'test3@mail.com', 2002);
INSERT INTO Orders VALUES (7, 'test2@mail.com', 2001);
INSERT INTO Orders VALUES (9, 'test2@mail.com', 2001);
INSERT INTO Orders VALUES (10, 'test3@mail.com', 2002);
INSERT INTO Orders VALUES (11, 'test4@mail.com', 2002);
INSERT INTO Orders VALUES (12, 'test4@mail.com', 2001);
INSERT INTO Orders VALUES (13, 'test4@mail.com', 2002);

--sql statement
select result.email from (
select email, year, count(*) As count from Orders where year in (2000,2001,2002)
group by year, email
)result
where 
(result.count = 1 and year = 2000)
;

Output:

email
test1@mail.com

Hassan_B0
Related