Sql Sum of child Items

Question

I need to add sum of child items to parent item. Child items 'childId' is same as parent item 'parentId'.

In the below table the ParentId '45627' has total count 0. But, I need as 11. 11 is sum of child items totalcount.

+----------+---------+-------+------------+  
| ParentId | ChildId | Name  | TotalCount |  
+----------+---------+-------+------------+  
|    45627 |   12568 | Test1 |          0 |  
|    52678 |   45627 | Test2 |          0 |  
|    23123 |   45627 | Test3 |          7 |  
|    54312 |   45627 | Test4 |          3 |  
|    32123 |   45627 | Test5 |          0 |  
|    12111 |   45627 | Test6 |          1 |  
|    32122 |   45627 | Test7 |          0 |  
|    43123 |   45627 | Test8 |          0 |  
+----------+---------+-------+------------+

Expected output :

+----------+---------+-------+------------+  
| ParentId | ChildId | Name  | TotalCount |  
+----------+---------+-------+------------+  
|    45627 |   12568 | Test1 |         11 |  
|    52678 |   45627 | Test2 |          0 |  
|    23123 |   45627 | Test3 |          7 |  
|    54312 |   45627 | Test4 |          3 |  
|    32123 |   45627 | Test5 |          0 |  
|    12111 |   45627 | Test6 |          1 |  
|    32122 |   45627 | Test7 |          0 |  
|    43123 |   45627 | Test8 |          0 |  
+----------+---------+-------+------------+

Though am able to achieve with self join but the performance is too slow.

Is there any other way to get the expected output with better performance.

The Query I have tried

SELECT a.parentId
    ,a.childId
    ,out1.TotalCount
FROM test a
LEFT JOIN (
    SELECT a.parentId
        ,sum(b.TotalCount) AS TotalCount
    FROM test a
    INNER JOIN test b ON a.ParentId = b.childId
    GROUP BY a.ParentId
    ) AS out1 ON a.ParentId = out1.ParentId

Note: The above tables are examples. The records count can be 10,000.

Show what have you try. That way we can give you other options instead of repeat the ones you already try. — Juan Carlos Oropeza, Oct 01 '15 at 19:22
I think you have ChildID and ParentID column reverse? 45627 looks like the parent of all those children. But we can wait for the dna result. — Juan Carlos Oropeza, Oct 01 '15 at 19:25
Where it says "ParentId" it should read "Id". Where it says "ChildId" it should read "ParentId". Show. Us. What. You. Have. Tried. — Mike Nakis, Oct 01 '15 at 19:26
Indent each line 4 space, or just select the text and press `ctrl-K` to apply format code — Juan Carlos Oropeza, Oct 01 '15 at 19:28
Possible duplicate of [Tree structure data query in SQL Server](http://stackoverflow.com/questions/10369634/tree-structure-data-query-in-sql-server) — Juan Carlos Oropeza, Oct 01 '15 at 19:29
This is the query, I have tried `select a.parentId, a.childId, out1.TotalCount from test a left join (select a.parentId, sum(b.TotalCount) as TotalCount from test a join test b on a.ParentId = b.childId group by a.ParentId ) as out1 on a.ParentId = out1.ParentId` — Bullet, Oct 01 '15 at 19:31
Related: [SQL SELECT Sum values without including duplicates](https://stackoverflow.com/q/31427071/3357935) — Stevoisiak, Dec 11 '20 at 19:29

Mike Nakis · Answer 1 · 2015-10-01T22:20:31.533

The column names make no sense, the input data make no sense, (but that's debatable,) and the desired output does not make any sense either. That's why this question has not received any answers yet.

Assuming that first of all, "ParentId" has been renamed to "Id", and "ChildId" has been renamed to "ParentId", so that the model makes sense, and so that the data kind of make sense, then the following:

CREATE TABLE TEST
(
    Id INTEGER, 
    ParentId INTEGER, 
    Name NVARCHAR(100), 
    TotalCount INTEGER 
);

INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 45627, 12568, 'Test1', 0 );
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 52678, 45627, 'Test2', 0 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 23123, 45627, 'Test3', 7 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 54312, 45627, 'Test4', 3 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 32123, 45627, 'Test5', 0 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 12111, 45627, 'Test6', 1 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 32122, 45627, 'Test7', 0 );  
INSERT INTO TEST( Id, ParentId, Name, TotalCount ) VALUES( 43123, 45627, 'Test8', 0 );  

SELECT Parent.Id, SUM( Child.TotalCount ) AS Total
   FROM TEST AS Parent 
       LEFT JOIN TEST As Child ON Parent.id = Child.ParentId
GROUP BY Parent.Id;

Produces the following output:

Id, Total
12111, NULL
23123, NULL
32122, NULL
32123, NULL
43123, NULL
45627, 11
52678, NULL
54312, NULL

Which is the correct output for the given input data, and according to the above assumptions.

It also happens to yield 11 for 45627, which is what the OP wanted.

If the question gets edited to make sense, I will amend my answer accordingly.

Wouldn't this give an incorrect total if you were joining multiple child tables? — Stevoisiak, Dec 11 '20 at 14:26
Well, it would give an incorrect total if the spline reticulation had a dividend, or if the flux capacitor exhibited condensation, but where did multiple child tables come from? — Mike Nakis, Dec 11 '20 at 15:07
I'm working on a similar issue to OP, only with a table (paycheck) that has multiple child tables to sum (earnings, taxes, deductions). — Stevoisiak, Dec 11 '20 at 19:27
@Stevoisiak you might want to post a separate question about that, show your table structures, and some sample data, so that your setup can be reproduced on sqlfiddle or similar website. — Mike Nakis, Dec 12 '20 at 21:15

score 0 · Answer 2 · edited Apr 29 '20 at 12:22

0

If I correctly understand your needs this query should meet your expectations:

SELECT x.parentId,
       x.childId,
       CASE WHEN x.total > 0 THEN x.total ELSE x.TotalCount END AS Total
FROM    
   (SELECT a.parentId
          ,a.childId,
          ,a.TotalCount
          ,(SELECT SUM(b.TotalCount) FROM test b WHERE b.childId = a.parentId) AS total
    FROM test a ) x

edited Apr 29 '20 at 12:22

answered Oct 01 '15 at 19:55

Rafał Wojtaszek

668
6
10

But this will update the totalcount for all records. I need for only parent. Hope my expected output is different from you query. – Bullet Oct 01 '15 at 20:11

Sql Sum of child Items

2 Answers2

Linked