Subtract multiple values returned by SQL query-CodePudding

I have a table transaction which has columns amount, date and type, type is an id field which belongs to choice earning, expenses, let's say id=1 is earning and id=2 is expenses.

I want to get monthly savings by:

savings = earning - expenses

I can get all the transactions separately by:

select amount from transaction where id='1'  -- will give earnings
select amount from transaction where id='2'  -- will give expenses

These two queries returns multiple results, because there might be many transactions of earning and expenses.

I got total savings by:

select (select SUM(transaction.amount) from transaction where id='1') - 
(select SUM(transaction.amount) from transaction where id='2') as savings;

BUT

I want to subtract transaction.amount from earning query result and expenses query result and group them by month so I'll have monthly savings.

How do I subtract two query results which returns multiple values and group them by date?

I'm using PostgreSQL

CodePudding user response：

You can do that in a single query:

select date_trunc('month', "date") as month, 
       SUM(transaction.amount) filter (where id = 1) 
        - 
       SUM(transaction.amount) filter (where id = 2) as savings
from transaction 
group by date_trunc('month', "date");

CodePudding user response：

intuitively I would tell you to try a solution like this:

SELECT tot_expense-tot_saving, tot_expense, tot_saving, month
FROM (select SUM(transaction.amount) tot_expense, month from transaction where 
id='1' group by month) as amount
LEFT JOIN (select SUM(transaction.amount) tot_saving, month from transaction 
WHERE id='2') as savings ON amount.month=savings.month

I'll put all field in SELECT for a control check