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