Aggregate total amount based on column value Postgres-CodePudding

I have this table:

 -------- ----------------- 
| amount | spendType |
 -------- ----------------- 
|     10 | Debit           |
|     5  | Credit          |
|    15  | Credit          |
|    20  | Debit           |
|     2  | Credit          |
 -------- -----------------

And this query:

SELECT
    sum(case
        when "spendType" = 'Credit' then "amount"
        when "spendType" = 'Debit' then "amount"
    else 0
    end) total, 
    "spendType" from transactions
    GROUP by "spendType"

Which yields the following:


 ---------------- ----------- 
|     total      | spendType |
 ---------------- ----------- 
| 1597160.000000 | Credit    |
|   11150.000000 | Debit     |
 ---------------- -----------

Which, is exactly what I've asked it to do. However, I'm actually looking to calculate a total amount for the query, ie. a balance, instead of a total "Credit" amount and a total "Debit" amount

How could I tweak my query to do so?

For reference, the final balance would be 1586010 (total Credit - total Debit)

CodePudding user response：

If you just need a "simple" balance the below query should suffice:

SELECT
    SUM(
        CASE WHEN spendType = 'Credit' THEN
            amount
        WHEN spendType = 'Debit' THEN
            - amount
        ELSE
            0
        END) AS balance
FROM
    transactions;

If you need json format you can use the below query:

SELECT
    json_build_object('balance', balance)
FROM (
    SELECT
        SUM(
            CASE WHEN spendType = 'Credit' THEN
                amount
            WHEN spendType = 'Debit' THEN
                - amount
            ELSE
                0
            END) AS balance
    FROM
        transactions) t;

Fiddle link (initial data by Rahul Biswas): https://dbfiddle.uk/?rdbms=postgres_11&fiddle=18a961c3ac24fed989bc9a5ce8039eb2

CodePudding user response：

Sum of debit and credit amount then subtract these two results.

-- PostgreSQL
SELECT SUM(CASE WHEN spendType = 'Credit' THEN amount ELSE 0 END)
          - 
        SUM(CASE WHEN spendType = 'Debit' THEN amount ELSE 0 END) balance
FROM transactions

Please check the url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c0f93820782b1402ca7d36e865fbfc70