Solved

Product Category Counts in Oracle Table

Posted on 2007-04-10
11
403 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

679 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