I have been looking around I cant seem to find help with this query so hopefully this is the right place to ask. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
I am trying to write a query to get the following output from a view I created that gives total sales by state and Product. I need to use the view to get maximum sales by state as shown below:
PRODUCT STATE TOTAL_SALES
------------------------------ --------------- -----------
Product 1 New York 6959
Product 2 New York 7191
Product 3 New York 6637
Product 4 New York 6344
Product 5 Washington 6510
Product 6 Washington 6168
So I created a view as follows:
Create View MaxSales as
Select Product, State, sum(sales) as Total_Sales
from ProdTable
Group By State, Product
order by Product, TOTAL_SALES DESC;
The View Gives me the data as such:
PRODUCT STATE TOTAL_SALES
------------------------------ --------------- -----------
Product 4 Oregon 6344
Product 4 New York 5861
Product 4 Washington 4915
Product 1 New York 6959
Product 1 Oregon 6465
Product 1 Washington 6301
Product 5 Washington 6510
Product 5 Oregon 6387
Product 5 New York 4193
Product 6 Washington 6168
Product 6 New York 6041
PRODUCT STATE TOTAL_SALES
------------------------------ --------------- -----------
Product 6 Oregon 4904
Product 2 New York 7191
Product 2 Oregon 6068
Product 2 Washington 5229
Product 3 New York 6637
Product 3 Oregon 6049
Product 3 Washington 4261
18 rows selected.
The following is correct but doesnt give me the states:
Select PRODUCT, MAX(TOTAL_SALES)
from MaxSales
Group By PRODUCT
;
PRODUCT MAX(TOTAL_SALES)
------------------------------ ----------------
Product 1 6959
Product 4 6344
Product 3 6637
Product 2 7191
Product 6 6168
Product 5 6510
6 rows selected.
When I try to add states, I get all 18 rows but I only need the top selling PROD for each state and this is where I am stuck:
Select state, PRODUCT, MAX(TOTAL_SALES) from MaxSales where state IN
(select distinct state from MaxSales)
Group By state, PRODUCT ;
STATE PRODUCT MAX(TOTAL_SALES)
--------------- ------------------------------ ----------------
Oregon Product 5 6387
Oregon Product 2 6068
Washington Product 1 6301
New York Product 1 6959
Washington Product 5 6510
Oregon Product 1 6465
Washington Product 4 4915
New York Product 3 6637
New York Product 2 7191
Washington Product 2 5229
Oregon Product 6 4904
STATE PRODUCT MAX(TOTAL_SALES)
--------------- ------------------------------ ----------------
Washington Product 6 6168
Washington Product 3 4261
New York Product 4 5861
New York Product 5 4193
New York Product 6 6041
Oregon Product 3 6049
Oregon Product 4 6344
18 rows selected.