Get total values of a parent and its child records

Question

I would appreciate any help on how to get totals of a parents and its children. I have two tables Units and UnitValues, one has parent child relation, and the second has only child and parent ids with associated values. I need to get a table that calculates total for a given parent and all its children.

I tried the following

;WITH tbl AS(
    SELECT ParentUnit,
    Sum(Value) AS Population,
    Sum(CASE WHEN Mark IN ('A','B') THEN Value ELSE 0 END) AS Mark
    FROM [TestDB].[dbo].[UnitValues] uv
    INNER JOIN [TestDB].[dbo].[Units] u
    On u.UnitID = uv.UnitID
    Group By ParentUnit
 )
 select * 
 from tbl
 where ParentUnit IN ('1TTTTT','2KKKKK')

It gives the incorrect result. The counts should be 6 instead of 2 for 2KKKKK, and 15 instead of 9 for 1TTTTT.

+--------+-------------------+--------+
| Unit   | Population   |        Mark |
+--------+-------------------+--------+
| 1TTTTT |                 9 |      9 |
| 2KKKKK |                 2 |      2 |
+--------+-------------------+--------+

Units table

+--------+----------+------------+
| UnitID |   Name   | ParentUnit |
+--------+----------+------------+
| 1TTTTT | Unit     | NULL       |
| 2KKKKK | Unit 1   | 1TTTTT     |
| 2LLLLL | Unit 2   | 1TTTTT     |
| 2NNNNN | Unit 3   | 1TTTTT     |
| 3KKKKG | Unit 1.2 | 2KKKKK     |
| 3KKKKS | Unit 1.1 | 2KKKKK     |
| 3LLLLL | Unit 2.1 | 2LLLLL     |
| 4LLLLL | Unit 2.2 | 3LLLLL     |
| 5LLLLL | Unit 2.3 | 4LLLLL     |
+--------+----------+------------+

UnitValues table

+-----+--------+---------+-------+------+
| ID  | UnitID | OtherId | Value | Mark |
+-----+--------+---------+-------+------+
| T12 | 1TTTTT | GGGGGG  |     1 |      |
| T22 | 2KKKKK | RRRRRR  |     1 | A    |
| T23 | 2KKKKK | RRRRRR  |     1 | A    |
| T24 | 2KKKKK | RRRRRR  |     1 | B    |
| T25 | 2KKKKK | RRRRRR  |     1 | A    |
| T31 | 2LLLLL | HHHHHH  |     1 | A    |
| T32 | 2LLLLL | HHHHHH  |     1 | A    |
| T33 | 2LLLLL | HHHHHH  |     1 | B    |
| T41 | 2NNNNN | HHHHHH  |     1 | A    |
| T42 | 2NNNNN | HHHHHH  |     1 | A    |
| T51 | 3KKKKG | BBBBBB  |     1 | A    |
| T52 | 3KKKKS | BBBBBB  |     1 | A    |
| T61 | 3LLLLL | BBBBBB  |     1 | A    |
| T71 | 4LLLLL | BBBBBB  |     1 | A    |
| T81 | 5LLLLL | BBBBBB  |     1 | A    |
+-----+--------+---------+-------+------+

Which part of your query is getting the children? I don't see any recursion. — Eric, Feb 26 '19 at 17:49
@Zaynul Abadin Tuhin Because there are 6 records in UnitValues table, 4 records for 2KKKKK and two for its children 3KKKKS and 3KKKKG — Tracy, Feb 26 '19 at 18:39
@Eric, I formatted my question, thanks. I can get all possible children for a parent, but no sure how combine it to get totals. I have all ids parents/children in UnitValues table — Tracy, Feb 26 '19 at 18:52

donPablo · Answer 1 · 2019-02-26T22:09:43.200

When I run this sql, I get these results

 SELECT 
    'Units=' as t1
    ,u.*
    ,'Values=' as t2
    ,uv.*

    --FROM [TestDB].[dbo].[UnitValues] uv
    FROM [StackOver].[dbo].[GetTotal_UnitValues] as uv

    --INNER JOIN [TestDB].[dbo].[Units] u  
    INNER JOIN  [StackOver].[dbo].[GetTotal_Units] as u
    On u.UnitID = uv.UnitID

    --Trim used because of the way excel data inserted to sql with surrounding blanks
    WHERE rtrim(Ltrim(Mark)) IN ('A','B')
    and  rtrim(Ltrim(ParentUnit)) IN ('1TTTTT','2KKKKK')

t1       UnitID      Name        ParentUnit     t2       ID         UnitID OtherId  Value   Mark
Units=   2KKKKK      Unit 1      1TTTTT         Values=  T22     2KKKKK      RRRRRR     1    A    
Units=   2KKKKK      Unit 1      1TTTTT         Values=  T23     2KKKKK      RRRRRR     1    A    
Units=   2KKKKK      Unit 1      1TTTTT         Values=  T24     2KKKKK      RRRRRR     1    B    
Units=   2KKKKK      Unit 1      1TTTTT         Values=  T25     2KKKKK      RRRRRR     1    A    
Units=   2LLLLL      Unit 2      1TTTTT         Values=  T31     2LLLLL      HHHHHH     1    A    
Units=   2LLLLL      Unit 2      1TTTTT         Values=  T32     2LLLLL      HHHHHH     1    A    
Units=   2LLLLL      Unit 2      1TTTTT         Values=  T33     2LLLLL      HHHHHH     1    B    
Units=   2NNNNN      Unit 3      1TTTTT         Values=  T41     2NNNNN      HHHHHH     1    A    
Units=   2NNNNN      Unit 3      1TTTTT         Values=  T42     2NNNNN      HHHHHH     1    A    
Units=   3KKKKG      Unit 1.2    2KKKKK         Values=  T51     3KKKKG      BBBBBB     1    A    
Units=   3KKKKS      Unit 1.1    2KKKKK         Values=  T52     3KKKKS      BBBBBB     1    A

revised Feb 26 14:00 PST

-- Can only do one at a time, since some rows may satisfy both   
DECLARE @FindID varchar(255) = '1TTTTT';  -- 15
--DECLARE @FindID varchar(255) = '2KKKKK';  -- 6

With descendants as
  ( select ParentUnit, UnitID as descendant, 1 as level , name
    from [StackOver].[dbo].[GetTotal_Units]
  union all
    select d.ParentUnit, s.UnitID, d.level + 1 , d.name
    from descendants as d
      join [StackOver].[dbo].[GetTotal_Units]s
        on d.descendant = s.ParentUnit
  ) 
select *
from descendants ddd
Inner Join [StackOver].[dbo].[GetTotal_UnitValues] uv
On ddd.descendant = uv.UnitID

Where (ParentUnit = @FindID
 Or descendant = @FindID)
 And ParentUnit is not null

Order by  ParentUnit , descendant, level;

Right, it gives you only first level for a parent, my trouble is how to use recursion to get all children for a parent together with a query to get totals. — Tracy, Feb 26 '19 at 19:16
@Tracy After you said " my trouble is how to use recursion to get all children for a parent together" I then did google on "sqlserver recursion to get all children" and picked the first item https:https://dba.stackexchange.com/questions/94932/getting-all-descendants-of-a-parent "Getting all descendants of a parent" which sounded like the right title. THEN, worked a lot on getting the ON for the inner join and the WHERE — donPablo, Feb 26 '19 at 22:11

score 0 · Answer 2 · answered Feb 26 '19 at 20:17

This query should give you the hierarchy. If you want information from the other table, you can add JOIN to the query.

;WITH cte AS (
    SELECT UnitID, ParentUnit, Name, 0 AS Level
    FROM Units
    WHERE ParentUnit IS NULL
    UNION ALL
    SELECT u.UnitID, u.ParentUnit, u.Name, Level + 1
    FROM Units u
    JOIN cte c ON u.ParentUnit = c.UnitID
 )
 SELECT *
 FROM cte
 ORDER BY Level

Get total values of a parent and its child records

2 Answers2