Search code examples
postgresqlgroup-byhaving

GROUP BY id HAVING value like 'X' or value like 'Y'

Table bo_sip_cti_event_day has columns: uuid, hangup_clause but I have many the same uuid in many records with other hangup_clause for example: a, ORIGINATOR_CANCEL, a, NO_ANSWER, a, ALLOTTED_TIMEOUT.

I have to get all uuid where hangup_cause is ORIGINATOR_CANCEL and NO_ANSWER for the same uuid.

So far I tried: select uuid from bo_sip_cti_event_day group by uuid having hangup_cause like 'ORIGINATOR_CANCEL' and hangup_cause like 'NO_ANSWER' but then the error says that hangup_cause have to be in group by clause or be in aggregation function.

Solution

  • In the HAVING clause you deal with aggregates. Conditional aggregates in your case:

    select uuid
from bo_sip_cti_event_day 
group by uuid
having count(case when hangup_cause = 'ORIGINATOR_CANCEL' then 1 end) > 0
   and count(case when hangup_cause = 'NO_ANSWER' then 1 end) > 0;

    This gives you all UUID for which both hangup clauses exist.