SQL Question

[Vicken]

Trying to do an SQL statement, but I can't figure it out. I need to find which product has the highest overall sales and which has the lowest overall sales.

I have these columns to work from

PRODUCT
ProductID, ProductName

ORDER
OrderID, Itemid, ProductID, Price, QTY, Itemtotal

(Its from the Oracle test database)

(Where itemtotal is qty x price)

basically I need to find a the sum of itemtotal, and the min and max of it and reference that back to the product ID, but I have no clue how. All I know is I need two subqueries

 

[Haircut]

There are a number of ways to acheive this, one way in Oracle is to use an analytic function (look them up in the help files if you don't know too much about them as they can be very handy)

The outer query is just to make sure we only get one row back here.

SELECT *
FROM
(SELECT FIRST_VALUE (productID) OVER (ORDER BY SUM(orde.ItemTotal)) Lowest_Sales
,FIRST_VALUE (productID) OVER (ORDER BY SUM(orde.ItemTotal) DESC) Highest_Sales
FROM Order orde
GROUP BY orde.ProductID
)
WHERE ROWNUM < 2

 

[Vicken]

I need to do it using straight SQL, no SQLplus commands since we are not learning that yet.

 

[chsh1ca]

This should do it, if I'm not tooo rusty:
For Top Seller:
SELECT p.ProductID, p.ProductName, SUM(o.QTY) AS SALES FROM ORDER o, PRODUCT p WHERE o.ProductID = p.ProductID GROUP BY p.ProductID ORDER BY SALES DESC LIMIT 1

For Bottom Seller:
SELECT p.ProductID, p.ProductName, SUM(o.QTY) AS SALES FROM ORDER o, PRODUCT p WHERE o.ProductID = p.ProductID GROUP BY p.ProductID ORDER BY SALES ASC LIMIT 1

The LIMIT directive works with MySQL, and I believe most ANSI-SQL compliant DBs, tho I could be really off there. Most of my work was with MSSQL Server 2000 and MySQL/PostgreSQL.

An alternate way might be with a subquery:
SELECT p.ProductID, p.ProductName, (SELECT SUM(o.QTY) as SALES FROM ORDER o WHERE o.ProductID = p.ProductID) FROM PRODUCT p ORDER BY SALES DESC LIMIT 1
And so forth.

 

[Vicken]

This is what I got so far.

I need to find the min and max of SUM(itemtot) this using subqueries only, straight ANSI SQL. 1 execution

SELECT product.prodid , descrip, SUM(itemtot)
FROM demo.item, demo.product
WHERE item.prodid = product.prodid
GROUP BY product.prodid, descrip

 

[Vicken]

Several hours later.

1 SELECT product.prodid , descrip, SUM(itemtot)
2 FROM demo.item, demo.product
3 WHERE item.prodid = product.prodid
4 GROUP BY product.prodid, descrip
5 HAVING SUM(itemtot) =
6 (SELECT MIN("SUM(ITEMTOT)")
7 FROM
8 (SELECT product.prodid , descrip, SUM(itemtot)
9 FROM demo.item, demo.product
10 WHERE item.prodid = product.prodid
11 GROUP BY product.prodid, descrip))
12 OR SUM(itemtot) =
13 (SELECT MAX("SUM(ITEMTOT)")
14 FROM
15 (SELECT product.prodid , descrip, SUM(itemtot)
16 FROM demo.item, demo.product
17 WHERE item.prodid = product.prodid
18 GROUP BY product.prodid, descrip))
19 ORDER BY SUM(itemtot)

Heh, shows you why manufactuerers came up with extensions!