Hierarchical roll-up in SQL accounting system

Question

I'm trying to make annual reports (Balance Sheet and Profit & Loss) from general journal entries in an accounting system.

The general journal table (simplified) includes:

  CREATE TABLE `sa_general_journal` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Date` timestamp NOT NULL DEFAULT current_timestamp(),
  `Item` varchar(1024) NOT NULL DEFAULT '',
  `Amount` decimal(9,2) NOT NULL DEFAULT 0.00,
  `Source` int(10) unsigned NOT NULL,
  `Destination` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Date` (`Date`),
  KEY `Source` (`Source`),
  KEY `Destination` (`Destination`),
  CONSTRAINT `sa_credit-account` FOREIGN KEY (`Destination`) REFERENCES `sa_accounts` (`ID`),
  CONSTRAINT `sa_debit-account` FOREIGN KEY (`Source`) REFERENCES `sa_accounts` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=21561 DEFAULT CHARSET=utf8;

where Amount is typically (but not necessarily) unsigned, and is an amount that is transferred from the Source account or category to the Destination category.

A chart of accounts (simplified) includes:

CREATE TABLE `sa_accounts` (
  `ID` int(10) unsigned NOT NULL,
  `Super` int(10) unsigned,
  `Name` varchar(255) NOT NULL,
  `Type` enum('Asset','Liability','Income','Expense'),
  `Report` enum('BS','PL'), -- for "Balance Sheet" or "Profit & Loss"
  PRIMARY KEY (`ID`),
  KEY `Super` (`Super`),
  CONSTRAINT `Super` FOREIGN KEY (`Super`) REFERENCES `sa_accounts` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

where ID is a seven-digit integer between 1,000,000 and 8,999,999, with a separate entry of zero for unallocated funds.

Account IDs that are divisible by 1,000,000 are "top level" accounts in a typical GAAP numbered-account scheme:

INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
  VALUES
    (0, NULL, "Not yet allocated", NULL, NULL),
    (1000000, NULL, "Assets", "Asset", "BS"),
    (2000000, NULL, "Liabilities", "Liability", "BS"),
    (3000000, NULL, "Equity", "Liability", "BS"),
    (4000000, NULL, "Income", "Income", "PL"),
    (5000000, NULL, "Expenses", "Expense", "PL"),
    (6000000, NULL, "Operating Expenses", "Expense", "PL"),
    (7000000, NULL, "Other Expenses", "Expense", "PL"),
    (8000000, NULL, "Other Income", "Income", "PL");

These roll-up accounts are abstract, and typically (but not necessarily) do not have anything actually allocated to them. Rather, sub-accounts receive actual allocations:

    INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
      VALUES
        (1010000, 1000000, "Cash", "Asset", "BS"),
        (1010001, 1010000, "Cash", "Asset", "BS"),
        (1010011, 1010000, "Chequing", "Asset", "BS"),
        (1019999, 1010000, "Test bank account", "Asset", "BS"),
-- ...
        (2100000, 2000000, "Accounts Payable", "Liability", "BS"),
        (2050000, 2100000, "Lines of credit", "Liability", "BS"),
        (2052008, 2050000, "Mastercard -2008", "Liability", "BS"),
        (2054710, 2050000, "Visa -4710", "Liability", "BS"),
-- ...
        (3200000, 3000000, "Shareholder Equity", "Liability", "BS"),
        (3300000, 3000000, "Rent to own", "Liability", "BS"),
-- ...
        (4050000, 4000000, "Dairy income", "Income", "PL"),
        (4050001, 4050000, "Animals sold", "Income", "PL"),
        (4050002, 4050000, "Milk sold", "Income", "PL"),
        (4050003, 4050000, "Cheese sold", "Income", "PL"),
        (4059999, 4050000, "Test income source", "Income", "PL"),
-- ...
        (5050000, 5000000, "Dairy expense", "Expense", "PL"),
        (5050001, 5000000, "Animals bought", "Expense", "PL"),
        (5050002, 5000000, "Feed bought", "Expense", "PL"),
        (5059999, 5000000, "Test expense destination", "Expense", "PL");
-- ...

These sub-accounts refer (via Super) to some other account in a hierarchical relationship. Note that the top-level accounts have NULL in the Super column.

So here's some test general journal entries:

INSERT INTO sa_general_journal (`ID`, `Date`, `Item`, `Amount`, `Source`, `Destination`)
  VALUES (NULL, "2020-05-03", "Test income transaction", 10.10, 4059999, 1009999),
 (NULL, "2020-05-03", "Test expense transaction", 1.01, 1009999, 5059999);

With the help of Nick, I was able to use a Common Table Expression to sum up general journal entries by the difference of Source and Destination accounts, using the following code:

WITH CTE1 AS (
    SELECT
        Source AS account,
        0 AS TYPE,
        -Amount AS Amount
      FROM sa_general_journal
    UNION ALL 
    SELECT
        Destination,
        1,
        Amount
      FROM sa_general_journal gj
    )
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      SUM(CASE WHEN CTE1.type = 0  THEN Amount END) AS Debits,
      SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
      SUM(Amount) AS Net
    FROM CTE1
      JOIN sa_accounts acc ON CTE1.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "PL"
    GROUP BY acc.ID

So far, so good! This was a huge help in my understanding of how Common Table Expressions can be used!

But now, I want to "roll up" the sub-accounts into the abstract accounts, giving a desired result similar to this:

NULL
ID Name Debits Credits Net 
1000000 Cash -1.01 10.10 9.09 
1009999 Cash -> Test chequing account -1.01 10.10 9.09
4000000 Income -10.10 NULL -10.10 
4050000 Income -> Dairy Income -10.10 NULL -10.10 
4059999 Income -> Dairy Income -> Test income transaction -10.10 NULL -10.10
5000000 Expenses -10.10 NULL -10.10 
5050000 Expenses -> Dairy Expenses -10.10 NULL -10.10 
5059999 Expenses -> Dairy Expenses -> Test expense transaction 1.01 1.01

After a few false starts, I came up with the following naive idea of simply wrapping a WITH RECURSIVE around the above code, but summing up the sub-accounts that have the same Super column:

WITH RECURSIVE CTE2 AS
  (WITH CTE1 AS (
    SELECT
        Source AS account,
        0 AS TYPE,
        -Amount AS Amount
      FROM sa_general_journal
    UNION ALL 
    SELECT
        Destination,
        1,
        Amount
      FROM sa_general_journal gj
    )
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      SUM(CASE WHEN CTE1.type = 0  THEN Amount END) AS Debits,
      SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
      SUM(Amount) AS Net
    FROM CTE1
      JOIN sa_accounts acc ON CTE1.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "BS"
    GROUP BY acc.ID
  UNION ALL
  SELECT
      Name,
      SUM(CTE2.Debits),
      SUM(CTE2.Credits),
      SUM(CTE2.Net)
    FROM CTE2
    WHERE CTE2.`Super` IS NOT NULL)    
SELECT * FROM CTE2

I'm aware the last SELECT has problems; as I said, this was my first attempt, but I appear to have run into an insurmountable roadblock.

When the preceding code is executed, I get "Query Failed. Restrictions imposed on recursive definitions are violated for table 'CTE2. Error code 4008." It took quite a bit of searching to figure out that aggregate queries (SUM, etc.) are not allowed in the recursive part of such a query. Sigh.

I've read that WITH RECURSIVE, SQL becomes Turing-compatible, so it must be possible to do what I'm looking for, but without SUM() in a recursive query, it's hard to imagine how to solve this!

user_9473764 · Answer

This query should give you the results you want. It is based on the answer to your previous question, with the addition of a recursive CTE that copies each transaction to all the accounts above it in the hierarchy. Values for each account are then summed in the final query:

WITH RECURSIVE xfers AS (
  SELECT Source AS account,
         0 AS TYPE,
         -Amount AS Amount
  FROM sa_general_journal
  UNION ALL 
  SELECT Destination,
         1,
         Amount
  FROM sa_general_journal gj
),
dbcr AS ( 
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      COALESCE(SUM(CASE WHEN x.type = 0  THEN Amount END), 0) AS Debits,
      COALESCE(SUM(CASE WHEN x.type = 1 THEN Amount END), 0) AS Credits,
      COALESCE(SUM(Amount), 0) AS Net
  FROM sa_accounts acc
  LEFT JOIN xfers x ON x.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "PL"
  GROUP BY acc.ID
),
summary AS (
  SELECT * 
  FROM dbcr
  WHERE Net != 0
  UNION ALL
  SELECT d.Account, d.Super, d.Name, s.Debits, s.Credits, s.Net
  FROM dbcr d
  JOIN summary s ON d.Account = s.Super
  WHERE s.Super IS NOT NULL
)
SELECT Account, Super, Name, 
       SUM(Debits) AS Debits,
       SUM(Credits) AS Credits,
       SUM(Net) AS Net
FROM summary
GROUP BY Account, Super, Name
ORDER BY Account

Output (for my expanded demo):

Account     Super       Name                Debits  Credits     Net
1000000     null        Assets              -6.31   10.1    3.79
1010000     1000000     Cash                -6.31   10.1    3.79
1010011     1010000     Chequing            -5.3    0       -5.3
1019999     1010000     Test bank account   -1.01   10.1    9.09
4000000     null        Income              -10.1   0       -10.1
4050000     4000000     Dairy income        -10.1   0       -10.1
4059999     4050000     Test income source  -10.1   0       -10.1
5000000     null        Expenses            0       6.31    6.31
5050002     5000000     Feed bought         0       5.3     5.3
5059999     5000000     Test expense dest   0       1.01    1.01

Demo on dbfiddle

user_343600 · Answer

Inspired by analyzing Nick's answer above, I'm learning more and more about WITH RECURSIVE!

This is a function I made to make a hierarchical name for the various accounts. Wherever Name is used in Nick's excellent answer, one can now use acct_name(ID) to get a hierarchical name path.

CREATE DEFINER=`root`@`10.1.2.%` FUNCTION `acct_name`(a int(10)) RETURNS varchar(253) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
RETURN
(WITH RECURSIVE acct_names AS (
SELECT id, Super, Name, Name AS Path FROM sa_accounts WHERE ID = a
UNION ALL
SELECT 
 sup.ID,
 sup.Super,
 sup.Name,
 CONCAT(sup.Name, ', ', acct_names.Path)
FROM acct_names
LEFT JOIN sa_accounts sup ON acct_names.Super = sup.ID
WHERE sup.ID IS NOT NULL
)
SELECT Path FROM acct_names WHERE `Super` IS NULL);

Now, the sample data will result in:

    Account     Super       Name                            Debits  Credits Net
    1000000     null        Assets                                      -6.31   10.1    3.79
    1010000     1000000     Assets, Cash                                -6.31   10.1    3.79
    1010011     1010000     Assets, Cash, Chequing                      -5.3    0       -5.3
    1019999     1010000     Assets, Cash, Test bank account             -1.01   10.1    9.09
    4000000     null        Income                                      -10.1   0       -10.1
    4050000     4000000     Income, Dairy income                        -10.1   0       -10.1
    4059999     4050000     Income, Dairy income, Test income source    -10.1   0       -10.1
    5000000     null        Expenses                                    0       6.31    6.31
    5050002     5000000     Expenses, Feed bought                       0       5.3     5.3
    5059999     5000000     Expenses, Test expense dest                 0       1.01    1.01

(I apologize if this is inappropriate as an answer. I did not see any way to post formatted code in the comments section.)

Hierarchical roll-up in SQL accounting system

2 Answers