November 4, 2003 at 12:18 pm
Calling all SQL Buffs. I am trying to figure how to write a SQL Statement and I will use Northwind database as example.
In Northwind database, I want to find the product that has generated the most revenue (ie. unitprice*quantity).
There is only one rule: if there are more than one product that generated the exact total, then must show all those products. I can tell you right now in the [order details] table there is only product that has a total. For discussion purposes, I am adding another record to the [order details] table so that there is a tie for first place.
Here is the code below:
-- cut here --
use northwind
go
BEGIN TRAN
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES(10248, 64, 126975.2, 1, 0)
SELECT PRODUCTID, SUM(Quantity*UnitPrice)
FROM [ORDER DETAILS]
GROUP BY PRODUCTID
HAVING SUM(QUANTITY*UNITPRICE) = (
SELECT MAX(THE_SUM)
FROM (
SELECT SUM(Quantity*UnitPrice) AS THE_SUM
FROM [ORDER DETAILS]
GROUP BY PRODUCTID
) X1
);
ROLLBACK;
-- cut here --
Note: it should be product IDs 38 and 64 that generate the most revenue each with $149984.20.
Just wondering, is there a more efficient way of doing it?
Thanks in advance,
Billy
November 4, 2003 at 1:21 pm
SELECT top 1 with ties
PRODUCTID, SUM(Quantity*UnitPrice)
FROM [ORDER DETAILS]
GROUP BY PRODUCTID
order by SUM(Quantity*UnitPrice) desc
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 5, 2003 at 4:08 pm
Thanks Steve. Both our queries execute with same query cost. The only difference is that your query has much better readility.
Billy
November 6, 2003 at 7:40 am
One reason I like poking around here regularly.
WITH TIES, didn't know about that one.
Except we're corporate casual, so we don't wear ties.
BOOO HISSS get the hook out.
KlK, MCSE
KlK
November 6, 2003 at 10:50 am
"With Ties" is one of the things learned right at the start, but use so infrequently it's easy to forget.
Thanks for reminding me.
Data: Easy to spill, hard to clean up!
November 6, 2003 at 11:19 am
quote:
One reason I like poking around here regularly.WITH TIES, didn't know about that one.
Except we're corporate casual, so we don't wear ties.
BOOO HISSS get the hook out.
KlK, MCSE
har har 🙂
Maybe it should be like going to fancy restaurant.
SELECT TOP 1 WITH SHIRTS AND SHOES
Because no shirt, no shoes = no service.
Then certain [order details] records would not get included in the GROUP BY clause.
B
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply