Peter Larsson Blog

Patron Saint of Lost Yaks

Sum up a tree hierachy in SQL Server 2005

by Peter Larsson in  , , ,

  -- Prepare sample data
DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11))

INSERT  @Accounts
SELECT  '100-000-000', NULL          UNION ALL
SELECT  '100-001-000', '100-000-000' UNION ALL
SELECT  '100-002-000', '100-000-000' UNION ALL
SELECT  '100-002-001', '100-002-000' UNION ALL
SELECT  '100-002-002', '100-002-000'

DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY)

INSERT  @Transactions
SELECT  '100-001-000', 1000.00 UNION ALL
SELECT  '100-002-001',  500.00 UNION ALL
SELECT  '100-002-002',  300.00

-- Setup staging expression
;WITH Yak (AccountNumber, Amount)
AS (
        SELECT          AccountNumber,
                        SUM(Amount) AS Amount
        FROM            @Transactions
        GROUP BY        AccountNumber

        UNION ALL

        SELECT          a.ParentAccountNumber,
                        y.Amount
        FROM            @Accounts AS a
        INNER JOIN      Yak AS y ON y.AccountNumber = a.AccountNumber
)

-- Show the expected resultset
SELECT          COALESCE(AccountNumber, 'All accounts') AS AccountNumber,
                SUM(Amount) AS Amount
FROM            Yak
WHERE           AccountNumber IS NOT NULL
GROUP BY        AccountNumber
ORDER BY        CASE
                        WHEN AccountNumber IS NULL THEN 1
                        ELSE 0
                END,
                AccountNumber

Legacy Comments
Joe Celko
2007-10-07		 re: Sum up a tree hierachy in SQL Server 2005
If you had used the nested sets model for the hierarchy, then the query would have been:

SELECT O2.acct_nbr, SUM(S1.acct_amt)
FROM Accounts AS A1, Accounts AS A2,
WHERE A1.lft BETWEEN O2.lft AND A2.rgt
AND A1.acct_nbr = S1.acct_nbr
GROUP BY A2.acct_nbr;

You would avoid complex constraints that you need to prevent cycles and orphans. The table would be normalized. The query will run faster than recursion. Etc.

Adjacency list models are a bad choice for computations.
Jeff
2007-10-12		 re: Sum up a tree hierachy in SQL Server 2005
Peso -- why all the NULL handling functions (COALESCE, the CASE expression) when you are not allowing any NULL values via the WHERE clause? Am I missing something or should it just be:

SELECT AccountNumber,
SUM(Amount) AS Amount
FROM Yak
WHERE AccountNumber IS NOT NULL
GROUP BY AccountNumber
ORDER BY AccountNumber

??
Peso
2007-10-13		 re: Sum up a tree hierachy in SQL Server 2005
Just for the case there are several "top accounts" to deal with.
Mahesh Jarange
2012-01-04		 re: Sum up a tree hierachy in SQL Server 2005
It's perfect solution to make Trial Balance and Balance Sheet in Financial Accounting Project.

Thanks to All.