Search code examples
sqlpostgresqlinner-joinaggregate-functionshaving-clause

List values per quantity and product type

I'm struggling with a task that most probably is an easy one. I want to list the client's age based on the products they have ordered - 4 bananas and 3 apples. The result should display the age column for the client who has bought a minimum of 4 bananas and 3 apples. Pineapples shouldn't be taken into consideration.

Result:

client_id   age
1           10-15

Clients table:

client_id   age
1   10-15
2   15-20

Products table:

product_id  client_id   product_name
1   1   banana
2   1   banana
3   1   banana
4   1   banana
5   1   apple
6   1   apple
7   1   apple
8   1   pineapple
9   2   apple
10  2   apple
11  2   banana
12  2   pineapple
Solution

  • Is this what you are looking for?

    select c.*
from products p join
     clients c
     using (client_id)
group by c.client_id
having count(*) filter (where product_name = 'banana') >= 4 and
       count(*) filter (where product_name = 'applies') >= 3;