How do I get the count for orders with a certain code only when there are multiple codes for the sam-CodePudding

Sorry this is my first post - please let me know if something doesn't make sense!

I'm trying to get a count of the number of orders with a specific code XXX ONLY

lets say table A looks something like this

|ORDER ID | ITEM CODE |
123         XXX    
123         YYY
123         YYY
456         XXX
456         XXX
456         XXX
789         XXX
000         YYY

what i want in the output is:

order 123 and 000 not to count
and order 456 and 789 to count as 1 each

I only want the count of the unique orders which have item code XXX ONLY

so the count/ output of the final query should be 2

currently what i have is

select order_id, item code, count(order_id) from table a
where item code = 'XXX' 
group by order_id, item code
order by count(order_id)

which outputs me the following

ORDER_ID  | ITEM CODE  |  COUNT(ORDER_ID)
 123        XXX            1
 345        XXX            3
 789        XXX            1

This is wrong because I want the output as described above

Thanks in advance!

CodePudding user response：

select order_id
from table_a
group by order_id
having min(item_code) = 'XXX'
   and max(item_code) = 'XXX'

CodePudding user response：

Seems like you want this :

select distinct order_id , item_code , 1 as count
from table t1
where not exists (
    select 1 from table t2 
    where t1.order_id = t2.order_id 
    and t2.item_code <> 'XXX'
)

the count would be always 1 per your question

CodePudding user response：

One option is to use an anti-join. For example:

select distinct t.order_id, 1 as cnt
from table_a t
left join table_a u on u.order_id = t.order_id and u.item_code <> 'XXX'
where t.item_code = 'XXX' and u.order_id is null

Result:

 ORDER_ID  CNT 
 --------- --- 
 789       1  
 456       1

See running example at db<>fiddle.

EDIT

To get the total count only, tweak the query as shown below:

select count(distinct t.order_id) as cnt
from table_a t
left join table_a u on u.order_id = t.order_id and u.item_code <> 'XXX'
where t.item_code = 'XXX' and u.order_id is null

Result:

CNT
---
2

See running example at db<>fiddle.