Search code examples
sqlibm-midrangedb2-400

Count in Query 400 not passing correctly

I Have a table that has order data like this: Table Order_Detail and Item_Master are joined by Item# We want to report on order number, Order_Detail table:

Order#     Item#              

1234       IPhone6 
1234       IPhone5
1234       Battery

join Item_Master:

Item#            Item_type    Desc

IPhone6          Phone        Smartphone
IPhone5          Phone        Smartphone

Now we only want order numbers, that have only one Item-Type = Phone. We are only interested in types Phone. I tried using Query/400 and doing a count on order# which = Phone, and then taking only the counts = 1. But this does bring in some orders that have more than one phone type = Phone, in our example here we would not want this order.

Solution

  • this query will return ordernums where the only ordered item type is 'phone'

    select ordernum
from order_detail od
join item_master im on im.itemnum = od.itemnum
group by ordernum
having count(case when im.item_type <> 'Phone' then 1 end) = 0
and count(*) = 1

    if you want to allow multiple 'phone' orders you can remove and count(*) = 1