EffinGood
asked on
SQL query help: SUM
Hi experts!
I have a SQL query that is not quite right, and I'm sure someone can give me the correct code. First, here's the test environment:
CREATE TABLE #customer (
ID int,
LastName varchar(20))
INSERT INTO #customer
VALUES (1, 'Jones'), (2, 'Smith')
CREATE TABLE #pies (
ID int,
Name varchar(20),
Price int)
INSERT INTO #pies
VALUES (1, 'Apple', 3), (2, 'Blueberry', 4), (3,'Cherry',5)
CREATE TABLE #orders (
ID int,
CustomerID int)
INSERT INTO #orders
VALUES (1,1),(2,2)
CREATE TABLE #pieorders (
ID int,
OrderId int,
PieId int)
INSERT INTO #pieorders
VALUES (1,1,1),(2,1,3),(3,2,1),(3 ,2,2),(3,2 ,3)
What I want is a query that will give me each order number, last name, and total price of the order. What I have now is this:
SELECT O.id, C.LastName,
SUM(P.Price) OVER (PARTITION BY O.id) AS TotalPrice
FROM #orders O
INNER JOIN #customer C ON O.CustomerID = C.ID
INNER JOIN #pieorders PO ON O.ID = PO.OrderId
INNER JOIN #pies P ON PO.PieId = P.ID
Which gives me the right columns and data, but returns one record per individual pie instead of one record per order. Help!
Note: the correct answer must not have any subqueries in the SELECT part of the statement (i.e. SELECT O.id, C.lastName, (SELECT sum(P.price) FROM....)
I have a SQL query that is not quite right, and I'm sure someone can give me the correct code. First, here's the test environment:
CREATE TABLE #customer (
ID int,
LastName varchar(20))
INSERT INTO #customer
VALUES (1, 'Jones'), (2, 'Smith')
CREATE TABLE #pies (
ID int,
Name varchar(20),
Price int)
INSERT INTO #pies
VALUES (1, 'Apple', 3), (2, 'Blueberry', 4), (3,'Cherry',5)
CREATE TABLE #orders (
ID int,
CustomerID int)
INSERT INTO #orders
VALUES (1,1),(2,2)
CREATE TABLE #pieorders (
ID int,
OrderId int,
PieId int)
INSERT INTO #pieorders
VALUES (1,1,1),(2,1,3),(3,2,1),(3
What I want is a query that will give me each order number, last name, and total price of the order. What I have now is this:
SELECT O.id, C.LastName,
SUM(P.Price) OVER (PARTITION BY O.id) AS TotalPrice
FROM #orders O
INNER JOIN #customer C ON O.CustomerID = C.ID
INNER JOIN #pieorders PO ON O.ID = PO.OrderId
INNER JOIN #pies P ON PO.PieId = P.ID
Which gives me the right columns and data, but returns one record per individual pie instead of one record per order. Help!
Note: the correct answer must not have any subqueries in the SELECT part of the statement (i.e. SELECT O.id, C.lastName, (SELECT sum(P.price) FROM....)
SELECT DISTINCT O.id, C.LastName,
SUM(P.Price) OVER (PARTITION BY O.id) AS TotalPrice
FROM #orders O
INNER JOIN #customer C ON O.CustomerID = C.ID
INNER JOIN #pieorders PO ON O.ID = PO.OrderId
INNER JOIN #pies P ON PO.PieId = P.ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points please!
(& I Hate to do this, but) Please don't do this:
SELECT DISTINCT O.id, C.LastName,
SUM(P.Price) OVER (PARTITION BY O.id) AS TotalPrice
the OVER() clause is deliberately allowing repetitive information, and the DISTINCT is deliberately suppressing repetitive information. Not only is this schizophrenic, it is extremely wasteful as both require resources, but only one wins in the end (distinct).
Much better to use "plain" SUM() and a group by clause.
(you had jumped to "advanced" use of SUM() by adding the OVER() clause)
(& I Hate to do this, but) Please don't do this:
SELECT DISTINCT O.id, C.LastName,
SUM(P.Price) OVER (PARTITION BY O.id) AS TotalPrice
the OVER() clause is deliberately allowing repetitive information, and the DISTINCT is deliberately suppressing repetitive information. Not only is this schizophrenic, it is extremely wasteful as both require resources, but only one wins in the end (distinct).
Much better to use "plain" SUM() and a group by clause.
(you had jumped to "advanced" use of SUM() by adding the OVER() clause)
for a giggle:
In truth, “select distinct” is a rather dull animal with minimal decision making powers.Select Distinct is returning duplicates ...
ASKER
This was not homework. This was a simplified query to try to get at the heart of the issue I was having.
I'm giving a B grade because, although your answer was the best of the bunch and gave the correct results, I believe it was still not a best practice. In a real life situation where I'm selecting a dozen columns from a dozen tables -- and where other developers might modify this query in the future to add/subtract those columns -- your approach is difficult to maintain because you have to group by every column you select except price. But you did start me thinking along the correct lines.
This is the approach I eventually used:
SELECT O.id, C.LastName,
Price.totalprice
FROM #orders O
INNER JOIN #customer C ON O.CustomerID = C.ID
INNER JOIN
(SELECT SUM(price) AS [totalprice], PO.orderid
FROM #pieorders PO
INNER JOIN #pies P ON PO.PieId = P.ID
GROUP BY PO.orderid)
AS Price ON Price.orderid = O.id
With this approach, I can easily add and subtract columns and join tables to the query without affecting the selected total price.
I'm giving a B grade because, although your answer was the best of the bunch and gave the correct results, I believe it was still not a best practice. In a real life situation where I'm selecting a dozen columns from a dozen tables -- and where other developers might modify this query in the future to add/subtract those columns -- your approach is difficult to maintain because you have to group by every column you select except price. But you did start me thinking along the correct lines.
This is the approach I eventually used:
SELECT O.id, C.LastName,
Price.totalprice
FROM #orders O
INNER JOIN #customer C ON O.CustomerID = C.ID
INNER JOIN
(SELECT SUM(price) AS [totalprice], PO.orderid
FROM #pieorders PO
INNER JOIN #pies P ON PO.PieId = P.ID
GROUP BY PO.orderid)
AS Price ON Price.orderid = O.id
With this approach, I can easily add and subtract columns and join tables to the query without affecting the selected total price.
Open in new window