Counting in Query 400 does not run correctly
I have a table with order data, for example: Table Order_Detail and Item_Master are connected by element # We want to report the order number, Table Order_Detail:
Order# Item#
1234 IPhone6
1234 IPhone5
1234 Battery
join Item_Master:
Item# Item_type Desc
IPhone6 Phone Smartphone
IPhone5 Phone Smartphone
Now we only need order numbers that only have one Item-Type = Phone. We are only interested in the types of phones. I tried using Query / 400 and did the counting in order # which = Phone and then only accepted counts = 1. But this results in some orders that have more than one type of phone = Phone, in our example here we won't want this order.
source to share
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 delete and count(*) = 1
source to share
Your question is a little confusing. Do you want to get the number of order numbers where Item_Type = 'Phone'
? If so, then you should work:
SELECT COUNT(DISTINCT OrderNum) AS OrderNumCount
FROM Order_Detail o
INNER JOIN Item_Master i ON o.ItemNum = o.ItemNum
WHERE Item_type = 'Phone'
Or, you only complete orders that are associated with only one record from the product table. If so, you can:
SELECT o.OrderNum
FROM Order_Detail o
INNER JOIN Item_Master i ON o.ItemNum = o.ItemNum
WHERE Item_type = 'Phone'
GROUP BY o.OrderNum
HAVING COUNT(*) = 1
source to share