Adding a subtotal to a column in a Firebird SQL query

Question

I need to see the subtotal in my AMOUNT field every time there is a change in the LEDGER_ACCOUNT FIELD

I have done a lot of research on this and it appears that this is a job for union. But I am unable to get the union to add the total every time there is a change

My current select query is as follows:

SELECT L.LEDGER_ACCOUNT, CURRROUND(L.DEBIT,2) - CURRROUND(L.CREDIT,2) AS AMOUNT FROM LEDGER L

At the moment it shows the following fields:

But I need the following return:

I am using Firebird 2.5.7.

In order to simplify my question, consider the following query: SELECT L.LEDGER_ACCOUNT, CURRROUND(L.DEBIT,2) - CURRROUND(L.CREDIT,2) AS AMOUNT FROM LEDGER L So basically I need a subtotal on the AMOUNT field every time there is a change in the L.LEDGER_ACCOUNT field. I will use an ORDER BY statement to sort by L.LEDGER_ACCOUNT. — gdekoker, Apr 27 '19 at 16:29
Please **edit** your question with additional information, don't put that in comments. It would be helpful if you provide example data and expected results, because right now it is not entirely clear to me what you want. Also specify the Firebird version you are using. — Mark Rotteveel, Apr 27 '19 at 17:25
Thank you for the feedback. I have made the changes as proposed. — gdekoker, Apr 27 '19 at 18:50

score 1 · Accepted Answer · answered Apr 28 '19 at 08:43

The result you want can be achieved in Firebird 2.5 with use of unions and some additional logic to be able to insert the subtotals and total in the right place. Using a simplified table LEDGER with the data as you have shown, you can use the following query:

SELECT 
    CASE LTYPE 
        WHEN 0 THEN CAST(ledger_account AS VARCHAR(50))
        WHEN 1 THEN 'Subtotal'
        WHEN 2 THEN 'TOTAL'
    END AS LEDGER_ACCOUNT_DISPLAY,
    AMOUNT
FROM (
    SELECT ledger_account, amount, 0 AS LTYPE
    FROM ledger
    UNION ALL
    SELECT ledger_account, SUM(amount), 1 AS LTYPE
    FROM ledger
    GROUP BY ledger_account
    UNION ALL
    -- BIN_SHL(1, 31) - 1 produces the maximum value of INTEGER; for sorting total at end
    SELECT BIN_SHL(1, 31) - 1, SUM(amount), 2 AS LTYPE
    FROM ledger
) a
ORDER BY ledger_account, LTYPE

With Firebird 3 you can also try a different approach using window functions. This solution will not produce additional rows for subtotals and total, but produce them inline. It could then be the responsibility of your presentation layer to show them correctly:

SELECT ledger_account, 
 amount, 
 SUM(amount) OVER(PARTITION BY ledger_account) AS subtotal, 
 SUM(amount) OVER() AS total
FROM ledger
ORDER BY ledger_account

Thank you sir. This worked perfectly. Now I need to start playing with adding additional fields. Thank you. — gdekoker, Apr 28 '19 at 14:13

Adding a subtotal to a column in a Firebird SQL query

1 Answers1