PostgreSQL – Subtraction based on column data simplifying query

I’m trying to subtract data from the table based on another column which represent their category.

For example:

+----+--------+----------+
| ID | Amount | Category |
+----+--------+----------+
| 1  |   100  |   Fish   |
+----+--------+----------+
| 2  |   200  |   Meat   |
+----+--------+----------+
| 3  |   300  |   Metal  |
+----+--------+----------+
| 4  |   400  |   Paper  |
+----+--------+----------+
| 5  |   500  |   Glass  |
+----+--------+----------+

I want to subtract the amount of Glass with the amount of Meat and Fish

I am able to write a simple query to subtract the data with one another but I was wondering if there was a way of simplifying the query.

The codes I have written:

create table calc(
    x int, 
    y int,
    z varchar
);

insert into calc values(1, 100, 'Fish');
insert into calc values(2, 200, 'Meat');
insert into calc values(3, 300, 'Metal');
insert into calc values(4, 400, 'Paper');
insert into calc values(5, 500, 'Glass');

select round(sum(y),2)
-(select round(sum(y),2) from calc where z = 'Fish')
-(select round(sum(y),2) from calc where z = 'Meat')
from calc
where z = 'Glass'

Answer

You can just use conditional aggregation:

select sum(case when z in ('Fish', 'Meat') then y
                when z in ('Glass') then - y
           end)
from calc
where z in ('Fish', 'Meat', 'Glass');

Strictly speaking the where clause is not necessary, but if you have a lot of values of z it can make the query more performant.

Advertisement

Answer