SQL statement for returning aggregate result that meets a condition across multiple years

user20015767 0
Viewed 30

Let's say this is my data set:

CREATE TABLE orders (
  `email` VARCHAR(11),
  `id` INTEGER,
  `year` INTEGER
);

INSERT INTO orders
  (`email`, `id`, `year`)
VALUES
  ('foo@bar.com', '1001', '2019'),
  ('foo@bar.com', '1002', '2019'),
  ('foo@bar.com', '1003', '2019'),
  ('foo@bar.com', '1004', '2020'),
  ('foo@bar.com', '1005', '2020'),
  ('foo@bar.com', '1006', '2020'),
  ('foo@bar.com', '1007', '2021'),
  ('foo@bar.com', '1008', '2021'),
  ('foo@bar.com', '1009', '2021'),
  ('bar@foo.com', '1111', '2019'),
  ('bar@foo.com', '1112', '2019'),
  ('bar@foo.com', '1113', '2019'),
  ('bar@foo.com', '1114', '2020'),
  ('bar@foo.com', '1115', '2020'),
  ('bar@foo.com', '1116', '2021'),
  ('bar@foo.com', '1117', '2021'),
  ('bar@foo.com', '1118', '2021');

I want to know which email addresses had 3 orders (id) in the ALL of the years 2019, 2020, and 2021.

I have this query:

SELECT email,year,count(*)
FROM (SELECT distinct email,id,year
    FROM orders) A
GROUP BY email,year
HAVING COUNT(*) = 3;

But what I want is just to return foo@bar.com since it's the only email that meets the condition of having 3 orders in each of the years.

mysql sql
1 Answers

You need to count twoce first get all email and years that have 3 orders and then count the years that have 3 orders

For Mysql 8

WITH CTE AS
(  SELECT  `email`, `year`
  FROM orders
  WHERE `year` IN(2019,2020,2021)
GROUP BY `email`, `year`
HAVINg COUNT(*) = 3)
SELECT `email` FROM CTE GROUP BY `email` HAVING Count(*) = 3
email
foo@bar.com

for Mysql 5.x

SELECT `email` FROM
(  SELECT  `email`, `year`
  FROM orders
  WHERE `year` IN(2019,2020,2021)
GROUP BY `email`, `year`
HAVINg COUNT(*) = 3)  CTE GROUP BY `email` HAVING Count(*) = 3
email
foo@bar.com

fiddle

nbk0
Related