Get the total Count and Sum of total Amount from down line record using SQL Server CTE-CodePudding

How can we Count and Sum all down-line rows to their up-line using SQL.

Current data:

  ST_ID    UPLINE   AMOUNT
  ---------------------------
  44930   52001    400
  52016   52001    300
  52001   9024     432
  76985   9024     100
  12123   35119    234
  12642   35119    213
  12332   23141    654

Here in above table, uplinedata 52001 two ST_ID with amount 400 and 300 each with total sum of 700 and again upline 9024 has ST_ID of 52001 with 432 700 with total of 1132.

Expected Output:

  UPLINE   AMOUNT  CNT
  ------------------------
  52001    700     2       (400  300 | 1 1)
  9024     1232    4       (700   432   100 | 2 1 1 = 4)
  35119    447     2       (234   213 | 1 1 = 2)
  23141    654     1

I thought of recursive CTE but could not able to gather the logic. Do anyone have any idea to achieve this. I am using SQL Server 2016.

CodePudding user response：

As I understood, the Upline column is connected to ST_ID column, and you want to find the sum and count grouped by (Upline all the matched values from ST_ID). i.e. Upline = 9024 is connected to ST_ID = 52001, so the sum for Upline = 9024 will be (432 100 from 9024 plus 300 400 from 52001).

You could use a recursive CTE as the following:

With CTE As
(
  Select ST_ID, Upline, Amount From table_name
  Union All
  Select T.ST_ID, T.Upline, C.Amount
  From table_name T Join CTE C
  On C.Upline = T.ST_ID
)
Select Upline, 
       Sum(Amount) As Amount, 
       Count(*) As Cnt
From CTE
Group By Upline

See a demo.

CodePudding user response：

With given sample data, use of a self-join query will get the desired output dataset.

Consider:

Query1: UplineAgg

SELECT 
  DISTINCT upline, 
  SUM(Amount) OVER(PARTITION BY upline) AS sum_upline_amount, 
  COUNT(Amount) OVER(PARTITION BY upline) AS count_upline
FROM uplinedata

Query2: UplineSJ

SELECT U.upline, U_1.upline AS upline2, Sum(U_1.Amount) AS SumAmt, Count(U_1.Amount) AS CntAmt 
FROM uplinedata AS U_1 
INNER JOIN uplinedata AS U ON U_1.upline = U.st_ID 
GROUP BY U.upline, U_1.upline

Query3:

SELECT UplineAgg.upline, [sum_upline_amount] ISNULL([SumAmt],0) AS S, [count_upline] ISNULL([CntAmt],0) AS C
FROM UplineSJ RIGHT JOIN UplineAgg ON UplineSJ.upline = UplineAgg.upline