Product Category Counts in Oracle Table

HI there,

I'm looking for the total orders with a specific category code. I have a Order detail table which has Order ID and Product Category field (Category CD= Field Name). In category CD field I have all categories including discaount code, and others products cat. What I like to do is get the counts of a specific product category e.g. the Category CD field has
'BK' = Book
'MG' = Magazine
'NSP' = Newspaper
'DISC' = Discount
'TAX' = Taxes
How can I get total orders for MG only not including all possible product combinations?
This must be done in Oracle - SQL or PL/SQL which ever is easier.

Thanks!
JoeSand2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select CategoryID, count(*) FROM Orders
group by categoryID
0
JoeSand2005Author Commented:
I'm looking for MG only. The field has for every order different product combination, because people purchase MG, BK and/ or NSP and those orders have tax code also. I'm looking for orders that have MG only.
Thanks!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you mean this:

select CategoryID, count(*)
FROM Orders
where categoryID = 'MG'
group by categoryID
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

JoeSand2005Author Commented:
Angel,
Your query will pull orders that may have any other product as oppose to MG only.
Any other idea,
0
JoeSand2005Author Commented:
Angel,
I'm sorry I should had given you more detail on this.  The Order detail table has a record for every product category in the Category_CD field.
Thanks!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you need something like this:

select CategoryID, count(*)
FROM Orders o
where categoryID = 'MG'
and not exists ( select null from orders i where i.order_id = o.order_id and i.categoryID <> 'MG' )
group by categoryID
0
JoeSand2005Author Commented:

I'm not too sure about that query;
What do you think about this one; If I want to get MG only

Select CategoryID, Count(*)
From Orders o,
Where OrderID  IN (
                                Select OrderID
                                From Oder_Detail
                                Where CategoryID = 'MG'
                                Minus
                                Select OrderID
                                Where CategoryID IN ('BK','NSP')
                               )
;

Would this work?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that can also work (with the detail that in the MINUS part the FROM is missing...

the "not exists" version is to be preferred, though:

select CategoryID, count(*)
FROM Orders o
where exists ( select null from order_details i where i.order_id = o.order_id and i.categoryID = 'MG' )
and not exists ( select null from order_details i where i.order_id = o.order_id and i.categoryID IN ('BK','NSP') )
group by categoryID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeSand2005Author Commented:
Angel,
Just one question, why you pick NULL in the Where exist clause between (Select NULL From ...)..?
why don't you pick OrderID isnted?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
because it is an EXISTS clause, you don't need to SELECT anything.
the WHERE clause makes the join between the outer select and the subselect (ie makes it a correlated subselect)
exists() and not exists() return true/false on the fact if there is at least one row returned or not, it does not matter which data is actually returned.
0
JoeSand2005Author Commented:
Thanks a lot Angel

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.