• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1523
  • Last Modified:

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
0
Danlo
Asked:
Danlo
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now