Solved

Product Category Counts in Oracle Table

Posted on 2007-04-10
11
392 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:JoeSand2005
  • 6
  • 5
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18884563
select CategoryID, count(*) FROM Orders
group by categoryID
0
 

Author Comment

by:JoeSand2005
ID: 18884737
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18884845
so you mean this:

select CategoryID, count(*)
FROM Orders
where categoryID = 'MG'
group by categoryID
0
 

Author Comment

by:JoeSand2005
ID: 18885050
Angel,
Your query will pull orders that may have any other product as oppose to MG only.
Any other idea,
0
 

Author Comment

by:JoeSand2005
ID: 18885106
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18885150
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
 

Author Comment

by:JoeSand2005
ID: 18889850

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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 18892956
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
 

Author Comment

by:JoeSand2005
ID: 18895132
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18895667
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
 

Author Comment

by:JoeSand2005
ID: 18901271
Thanks a lot Angel

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now