?
Solved

SQL query help: SUM

Posted on 2013-11-14
6
Medium Priority
?
735 Views
Last Modified: 2013-11-15
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....)
0
Comment
Question by:EffinGood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39649991
SELECT *
FROM 
(
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
) a
GROUP BY a.ID,a.LastName,a.TotalPrice

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 39649993
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

Open in new window

0
 
LVL 16

Accepted Solution

by:
DcpKing earned 1500 total points
ID: 39650016
Step one: get all the info, albeit not quite in the right format:
select o.id, o.CustomerID, c.LastName, po.PieId, p.Name, p.Price 
from #orders o
inner join #customer c		on o.CustomerID = c.id
inner join #pieorders po	on po.OrderId = o.id
inner join #pies p		on po.PieId = p.id

Open in new window

Step two: Arrange to get the summary fields you want:
select o.id, c.LastName, sum(p.Price )
from #orders o
inner join #customer c		on o.CustomerID = c.id
inner join #pieorders po	on po.OrderId = o.id
inner join #pies p		on po.PieId = p.id
group by o.id, c.LastName

Open in new window

So, as you see, very simple SQL - no partitions or CTE - will give what you're looking for. See the attached screenshot...


hth

Mike

(PS this sounds like homework, with the restriction: you should be doing your homework yourself! :)  )
Screenshot-from-2013-11-14-20-57.png
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39650047
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)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39650053
for a giggle:
In truth, “select distinct” is a rather dull animal with minimal decision making powers.
Select Distinct is returning duplicates ...
0
 

Author Closing Comment

by:EffinGood
ID: 39651956
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question