skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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]

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search