SQL query with having function to only includes parts of another column?

Question

I am trying to find fact_order_id's that have beverage only items. This would make the fact_order_id not include any food items.

fact_order_id
fact_order_line_id
category Group
category Name

I have used 2 separate SQL Queries below. When I go back and check my work, I notice some fact_order_id's in the output aren't beverage only orders. Here are the 2 separate SQL Queries that give me the same output:

select fact_order_id
from my_table
group by fact_order_id
having avg((category_group like 'Beverage%')::int) = 1;


select fact_order_id
from my_table
group by fact_order_id
having avg(CASE WHEN category_group like 'Beverage%' THEN 1 ELSE 0 END)=1;

Both of the outputs above give me the same number of rows. Whenever I cross check the fact_order_id, some of them would include an output with food items.

This fact_order_id shouldn't be included in my output since there are other non-beverage items on the order. There are 2 Category Group that have a beverage name (Beverage (non-alcohol) & Beverage (specialty)). Is there a better way of creating a SQL Query to only have an output of fact_order_id's that are beverage only orders?

I am using PostgreSQL Version: 08.00.0002

Answer 1

Based on how old your PostgreSQL is, can you please try this?

select fact_order_id
  from my_table
 group by fact_order_id
having bool_and((category_group like 'Beverage%'));

Based on your comment, can you please try this query and share your results?

select fact_order_id, category_group, category_name,
       (category_group like 'Beverage%') as is_beverage
  from my_table
 where fact_order_id = '1 292 757'

You can join back into your table in your checks to see if there is a problem. The following query is nonsensical from a SQL standpoint, but it would automate your cross-checking:

with bev_orders as (
  select fact_order_id
    from my_table
   group by fact_order_id
  having bool_and((category_group like 'Beverage%'))
)
select *
  from my_table t
  join bev_orders b 
    on b.fact_order_id = t.fact_order_id
 where t.category_group not like 'Beverage%' ;