In MySQL, return records with specific counts by year-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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, '[email protected]', 2000);
INSERT INTO Orders VALUES (2, '[email protected]', 2001);
INSERT INTO Orders VALUES (3, '[email protected]', 2001);
INSERT INTO Orders VALUES (4, '[email protected]', 2002);
INSERT INTO Orders VALUES (5, '[email protected]', 2001);
INSERT INTO Orders VALUES (6, '[email protected]', 2002);
INSERT INTO Orders VALUES (7, '[email protected]', 2001);
INSERT INTO Orders VALUES (9, '[email protected]', 2001);
INSERT INTO Orders VALUES (10, '[email protected]', 2002);
INSERT INTO Orders VALUES (11, '[email protected]', 2002);
INSERT INTO Orders VALUES (12, '[email protected]', 2001);
INSERT INTO Orders VALUES (13, '[email protected]', 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
[email protected]