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

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;

Collectives™ on Stack Overflow

Return two values using one sql in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related