Sum of column in child table alongside parent table in view

Hello Experts,

I need the most efficient way (for the server, not for me) to sum all child rows for a given parent row, and display that sum alongside the parent row's columns in a view.

ie: I have the following Parent Table:
pkParentID
DateColumn1
DataColumn2
DataColumn3
...etc...

And a child table such as:
pkChildID
fkParentID
ChildValueColumn

What I need is a view containing the following columns:
pkParentID
DateColumn1
DataColumn2
DataColumn3
[Sum of all children's ChildValueColumn values]

What is the best method (least amount of processing for the server) to get sum of the child rows' ChildValueColumn column?

Thanks in advance,
-Danlo
LVL 1
DanloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Calculate the sum of the childvaluecolumn as a subquery, then join it on the main, and select the result
SELECT 
   p.pkParentID,
   p.DateColumn1,
   p.DataColumn2,
   p.DataColumn3,
   c.ChildValueColumnSum
FROM [Parent Table] p
   JOIN (
      SELECT pkParentID, SUM(ChildValueCOlumn) as c.ChildValueColumnSum
      FROM [Child Table] 
      GROUP BY pkParentID) c ON p.pkParentID = c.fkParentID

Open in new window

0
Shaun KlineLead Software EngineerCommented:
The GROUP BY clause would the method to use.

For your example:
SELECT P.pkParentID, P.DateColumn1, P.DataColumn2, P.DataColumn3, SUM(C.ChildValueColumn)
FROM Parent P
   INNER JOIN Child C ON P.pkParentID = C.fkParentID
<WHERE CLAUSE HERE>
GROUP BY P.pkParentID, P.DateColumn1, P.DataColumn2, P.DataColumn3
<HAVING CLAUSE HERE>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanloAuthor Commented:
Jim / Shaun,

Does it make a significant difference whether you nest a Select statement inside another, as in Jim's solution, vs an inline SUM like in Shaun's?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Compare the actual query plans. Under normal circumstances not.
0
Shaun KlineLead Software EngineerCommented:
There are probably performance differences. I believe the SQL engine will do the subquery in Jim's solution first and then join the results to the parent table. It all depends on how the SQL engine builds the performance plan.
0
DanloAuthor Commented:
Thanks folks!

Splitting points between the two solutions because both seem equally valid and came in seconds apart from each other. Marking Shaun's solution as 'best' because the inline SUM appears to be more efficient according to the plan, at least in my scenario.
0
ste5anSenior DeveloperCommented:
The optimizer is so good, that query plans normally don't differ. E.g.

USE AdventureWorks2012;

SELECT  SOH.SalesOrderID ,
        SOH.RevisionNumber ,
        SOH.OrderDate ,
        SOH.SalesOrderNumber ,
        SOH.PurchaseOrderNumber ,
        SOH.SubTotal ,
        SUM(SOD.LineTotal) SumLineTotal
FROM    Sales.SalesOrderHeader SOH
        INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SOH.SalesOrderID
GROUP BY SOH.SalesOrderID ,
        SOH.RevisionNumber ,
        SOH.OrderDate ,
        SOH.SalesOrderNumber ,
        SOH.PurchaseOrderNumber ,
        SOH.SubTotal;

SELECT  SOH.SalesOrderID ,
        SOH.RevisionNumber ,
        SOH.OrderDate ,
        SOH.SalesOrderNumber ,
        SOH.PurchaseOrderNumber ,
        SOH.SubTotal ,
        Q.SumLineTotal
FROM    Sales.SalesOrderHeader SOH
        INNER JOIN ( SELECT SOD.SalesOrderID ,
                            SUM(SOD.LineTotal) AS SumLineTotal
                     FROM   Sales.SalesOrderDetail SOD
                     GROUP BY SOD.SalesOrderID
                   ) Q ON Q.SalesOrderID = SOH.SalesOrderID;

Open in new window



gives us Actual execution query plan
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your code.  -Jim
0
DanloAuthor Commented:
ste5an, that is impressive how close (in this case seemingly identical) both of those plans are! Edit: proper spelling of ste5an.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.