Solved

Product Category Counts in Oracle Table

Posted on 2007-04-10
11
402 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 143

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 143

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

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 143

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 143

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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