Postgresql Select and display one column based on another column

Question

ID IS_SPEND HOWMUCH

I want to calculate how much money I have after several transactions.

spend as spend money and not spend as earn money

I tried

select is_spend, sum(howmuch) from table group by is_spend

But it cannot reach my goal.

I don't know how can I get the result I want,

just output 495 is perfect.

Gordon Linoff · Accepted Answer · 2019-12-20 01:50:50Z

2

You seem to want aggregation:

select sum(case when is_spend then -howmuch else howmuch end)
from table

answered Dec 20, 2019 at 1:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2019-12-20 02:05:21Z

1

Could subtract two conditional sums:

SELECT COALESCE (sum(howmuch) FILTER (WHERE NOT IS_SPEND), 0)
     - COALESCE (sum(howmuch) FILTER (WHERE IS_SPEND), 0)  AS total
FROM   tbl;

But you'll need to add COALESCE if you cannot be certain that both types exist.

answered Dec 20, 2019 at 2:05

668k159 gold badges1.2k silver badges1.3k bronze badges

Satriyo Putra Sarjono · Accepted Answer · 2019-12-20 02:45:58Z

1

try this:


SELECT (SELECT SUM(HOWMUCH) FROM TABLE WHERE IS_SPEND = '0') - (SELECT SUM(HOWMUCH) FROM TABLE WHERE IS_SPEND = '1') AS GOAL;

answered Dec 20, 2019 at 2:45

112 bronze badges