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
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%' ;