CODEWITHSUNDEEP
sqlsql-server
Bullet
Bullet

Reputation: 87

Sql Sum of child Items

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.

Upvotes: 1

Views: 6420

Answers (2)

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

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

Upvotes: 0

Mike Nakis
Mike Nakis

Reputation: 61986

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.

Upvotes: 2

Related Questions