Aggregation without using subqueries

user_5436452 0
Viewed 35

Table drinks:

person_id type drunk
1 COKE true
1 FANTA false
2 TIMER false
2 COKE true
2 PEPSI false
2 PEPSI false
3 TIMER false
3 FANTA false
3 COKE true
3 COKE true
3 PEPSI true
4 TIMER false
4 COKE true

Goal:

person_id type
3 TIMER
4 TIMER

There is a table with the drinks of persons. A person can have multiple types of multiple drinks which can be drunk. Some persons do have a TIMER which tolds them when to drink. My goal is to create a query - without a subquery - thats lists me all persons timers, who drunk all dark drinks the person posses.

Explanation:

  • Person 1 has no TIMER -> irrelevant
  • Person 2 has a TIMER, but has two undrunken PEPSI -> irrelevant
  • Person 3 has a TIMER, has drunk his COKE's & PEPSI (FANTA is not important here) -> yes please
  • Person 4 has a TIMER, has only a drunken COKE -> yes please

I've already a solution for this, but I want it without a Subquery:

SELECT *
FROM drinks d LEFT JOIN (
        SELECT person_id,
               array_agg(type) AS agg_types,
               array_agg(drunk) AS agg_drunk
        FROM drinks 
        WHERE type::TEXT = ANY(ARRAY['COKE', 'PEPSI'])
        GROUP BY person_id
        ) AS sub ON d.person_id = sub.person_id
WHERE d.type = 'TIMER'
  AND TRUE = ALL(sub.agg_drunk)
  AND FALSE = d.drunk

I look forward to any help, thank you in advance

postgresql sql
2 Answers

If I understand correctly, you can use group by and having:

select person_id, 'TIMER'
from drinks d
group by person_id
having count(*) filter (where d.type = 'TIMER') > 0 and
       count(*) filter (where d.type in ('Coke', 'Pepsi') and drunk) = 2;

Note: In your example data there are no duplicates. If that is possible, then the second condition would use count(distinct):

       count(distinct d.type) filter (where d.type in ('Coke', 'Pepsi') and drunk) = 2;
user_11440350

You can use a anti-join to find who hasn't drank their dark drinks.

For example:

select d.person_id, d.type
from drinks d
left join (
  select person_id
  from drinks
  where type in ('COKE', 'PEPSI') and drunk = 'false'
) u on u.person_id = d.person_id
where d.type = 'TIMER'
  and u.person_id is null
user_64361910
Related