SQL Query (max of sum per year)
I will try to describe my problem here. Let's say that i have 3 tables:
1. sales
salesID (PK)
productID(FK) - FK that points to the sold product
storeID(FK) - FK that points to the store that sold the product
month - month in which the product was sold
year - year in which the product was sold
amount - amount(of money) for which the product was sold
2. product
productID (PK)
productName
3. store
storeID (PK)
storeName
I need the following query: For every store show the top selling product
in that store for the specified year. So the result should look something
like:
STORE PRODUCT AMOUNT
store1 product1 XXX amount
store2 product2 YYY amount
store3 product1 XYX amount
Where each amount would be the highest sum of all amounts in that year.
What I can do now is:
SELECT store.storeName
, product.ProductName
, SUM(sales.ammount)
FROM sales
JOIN product ON sales.productID = product.productID
JOIN store ON sales.storeID = store.storeID
GROUP BY store.storeName
, product.ProductName
WHERE sales.year = 'XXXX'
;
Where I get a sum per product and per store. After this I can use the
cursor to go thru the entire table row by row and check which one has the
highest amount for that store.
But I was wondering if it is possible to do that in a 'simple' query?
I'm using SQL Developer for Oracle 11g database. And I would appreciate
any help.
No comments:
Post a Comment