SQL server query to Calculate SUM of Child values in a Parent Child Table. Exclude Child's value from the sum if the parent has a value assigned

clj7thdc 于 2023-04-28 发布在 SQL Server

关注(0)|答案(1)|浏览(135)

I have a table Pools where I need to show sum of child to each parent. If the grandchild's parent has a value than exclude grandchild's value from the sum

Table script and data

CREATE TABLE [dbo].[Pools](
    [id] [int] NULL,
    [Pool] [varchar](50) NULL,
    [ParentPool] [varchar](50) NULL,
    [Amount] [int] NULL
)

INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (1, N'ROOT', NULL, NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (2, N'Pool 1', N'ROOT', 10000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (3, N'Pool 1.1', N'Pool 1', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (4, N'Pool 1.2', N'Pool 1', 2000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (5, N'Pool 1.3', N'Pool 1', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (6, N'Pool 1.3.1', N'Pool 1.3', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (7, N'Pool 1.3.2', N'Pool 1.3', 500)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (8, N'Pool 1.3.3', N'Pool 1.3', 5000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (9, N'Pool 2', N'ROOT', 35000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (10, N'Pool 2.1', N'Pool 2', 28000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (11, N'Pool 3', N'ROOT', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (12, N'Pool 3.1', N'Pool 3', 4500)
GO

Way I tried

WITH CTE AS (
    SELECT pool, ParentPool, isnull(Amount,0.0)Amount
    FROM Pools
    WHERE ParentPool IS NULL 
    UNION ALL
    SELECT t.Pool, t.ParentPool, isnull(t.Amount,0.0)Amount
    FROM Pools t
    INNER JOIN CTE r ON t.ParentPool = r.pool
    WHERE NOT EXISTS (
        SELECT 1
        FROM Pools t2
        WHERE t2.Pool = t.ParentPool
        AND isnull(t2.Amount,0.0) > isnull(r.Amount,0.0) 

    )
)

SELECT Pool, ParentPool, isnull(Amount,0.0)Amount, (
        SELECT SUM(isnull(Amount,0.0))
        FROM CTE
        WHERE Pool IN (
            SELECT Pool
            FROM CTE
            WHERE ParentPool = t.Pool 
        )
    ) AS CalculatedAmount
FROM CTE t

Expected output

Pool Name	Amount	Calculated amount
ROOT		49500
Pool 1	10000	5000
Pool 1.1		0
Pool 1.2	2000	0
Pool 1.3	3000	5500
Pool 1.3.1		0
Pool 1.3.2	500	0
Pool 1.3.3	5000	0
Pool 2	35000	28000
Pool 2.1	28000	0
Pool 3		4500
Pool 3.1	4500	0

Image illustration

Thanks in advance

sql-server

来源：https://stackoverflow.com/questions/76063539/sql-server-query-to-calculate-sum-of-child-values-in-a-parent-child-table-exclu

1条答案

按热度按时间

6jjcrrmo1#

Answer by @lptr:

Use a recursive CTE

with c as (
    select *,
      Pool as cPool,
      Amount as cAmount,
      case when Amount is not null then 1 else 0 end as stopc,
      0 as pAmount,
      1 as plvl
    from dbo.Pools

    union all

    select
      c.id,
      c.Pool,
      c.ParentPool,
      c.Amount,
      p.Pool,
      case when c.stopc = 1 then 0 else p.Amount end,
      case when c.stopc = 1 or p.Amount is not null then 1 else 0 end,
      case when c.plvl = 1 then p.Amount end,
      c.plvl + case when p.Amount is null then 0 else 1 end
    from c
    join dbo.Pools as p on c.cPool = p.ParentPool
)
select
  id,
  Pool,
  ParentPool,
  Amount,
  case when Amount is null then sum(cAmount) else sum(pAmount) end
from c
group by
  id,
  Pool,
  ParentPool,
  Amount;

赞(0）回复(0）举报 2023-04-28

我来回答

SQL server query to Calculate SUM of Child values in a Parent Child Table. Exclude Child's value from the sum if the parent has a value assigned

1条答案

相关问题

热门标签

最新问答