How to sum all columns value of a table and display the total in new row by using Postgresql

Question

I am using normal select query to display all rows

SELECT 
    type,
    debit,
    credit,
    (debit-credit) as balance
from bank_cash_registers

Its displayed like below image

Now I need to display this total as an additional row with help of Postgresql query like below image. How can I achieve this?

and also is there any option for separate total based on type like below..

Answer 1

Another way to do this is by using grouping sets. Its advantage is that it can easily be extended. Furthermore I think it was created specifically for this purpose.

This should be more efficient than the UNION solution as the data is passed through only once.

The following query returns what you want:

SELECT COALESCE(type, 'Total: '), SUM(debit), SUM(credit), SUM(debit - credit) AS balance
FROM bank_cash_registers
GROUP BY GROUPING SETS ((type, debit, credit), ());

The following query groups together the values having the same type (notice that the only thing that changed is the GROUPING SETS clause):

SELECT COALESCE(type, 'Total: '), SUM(debit), SUM(credit), SUM(debit - credit) AS balance
FROM bank_cash_registers
GROUP BY GROUPING SETS ((type), ());

Result:

bank    0    1500    -1500
cash    0     700     -700
Total:  0    2200    -2200

Your updated question can be solved that way as follows:

SELECT
  CASE WHEN GROUPING(debit) > 0 THEN 'Total: ' ELSE type END AS type,
  SUM(debit), SUM(credit), SUM(debit - credit) AS balance
FROM bank_cash_registers
GROUP BY GROUPING SETS ((type, debit, credit), (type));

You can even add the big total with

(...) GROUPING SETS ((type, debit, credit), (type), ());