Return two values using one sql in PostgreSQL

Question

My table with user transactions in postgres looks like this:

   id  sender_id    recipient_id           amount_money
   --- -----------  ---------------------- -----------------
   1   1            2                      60.00
   2   1            2                      15.00
   3   2            1                      35.00

The user with id number 2 has 75.00 revenues and 35.00 expenses

I would like to get a result similar to this:

[{name: 'revenues', value: 75.00}, {name:'expenses', value: 35.00}]

Can such result be done in postgres sql itself? I think about array_agg function. If it is difficult, I can handle it in javascript, but I need a query that will return two values to me: revenues and expenses

Gordon Linoff · Accepted Answer · 2020-04-21 22:28:04Z

2

Here is one method:

select sum(amount) filter (where recipient_id = 2) as revenues,
       sum(amount) filter (where sender_id = 2) as expenses
from transactions t
where 2 in (sender_id, recipient_id);

If you wanted to do this for more than one person, then you can use a lateral join and aggregation:

select v.id, sum(v.revenue) as revenues, sum(v.expenses) as expenses
from transactions t cross join lateral
     (values (t.recipient_id, t.amount, 0), (t.sender_id, 0, t.amount)
     ) v(id, revenue, expense)
group by v.id;

answered Apr 21, 2020 at 22:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Return two values using one sql in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related