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
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).
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.
Output:
email
[email protected]