Solved

Product Category Counts in Oracle Table

Posted on 2007-04-10
11
399 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

914 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

19 Experts available now in Live!

Get 1:1 Help Now