CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3postgresql-9.4
Mani
Mani

Reputation: 2655

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

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 enter image description here

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

enter image description here

and also is there any option for separate total based on type like below.. enter image description here

Upvotes: 1

Views: 3985

Answers (2)

Vivek S.
Vivek S.

Reputation: 21963

SELECT type
    ,debit
    ,credit
    ,(debit - credit) as balance
FROM bank_cash_register

UNION ALL

SELECT 'Total: '
    ,sum(debit)
    ,sum(credit)
    ,sum((debit - credit))
FROM bank_cash_register

To group the total based on Type column 

SELECT *
FROM (
    SELECT type
          ,debit
          ,credit
          ,(debit - credit) as balance
    FROM bank_cash_register

    UNION ALL

    SELECT type || '_total'
          ,sum(debit)
          ,sum(credit)
          ,sum((debit - credit))
    FROM bank_cash_register
    GROUP BY 1
    ) t
ORDER BY split_part(type, '_', 1)

Go through split_part()

Demo

Upvotes: 2

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

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), ());

Upvotes: 4

Related Questions