Postgres - add or subtract rows, depending on column value

Question

I have a table shipments:

--------------------------
id | event    | quantity |
--------------------------
1  | received | 20       |
2  | sent     | 30       | 
3  | received | 45       |

I want a query that will sum all the received quantities, and subtract that value from the sent quantities. The query should return a result of 35.

SELECT SUM(quantity)
FROM shipments
WHERE event = 'received';

This query returns 65. How can I get it to also subtract the sent quantity (and get a result of 35)?

sticky bit · Accepted Answer · 2021-01-31 17:21:42Z

6

Use a CASE expression that puts a sign depending on event.

SELECT sum(CASE event
             WHEN 'received' THEN
               quantity
             WHEN 'sent' THEN
               -quantity
             ELSE
               0
           END)
       FROM shipments;

answered Jan 31, 2021 at 17:21

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Postgres - add or subtract rows, depending on column value

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