Calculate differences between rows while grouping with SQL
- by Guido
I have a postgresql table containing movements of different items (models) between warehouses.
For example, the following record means that 5 units of model 1 have been sent form warehouse 1 to 2:
source target model units
------ ------ ----- -----
1 2 1 5
I am trying to build a SQL query to obtain the difference between units sent and received, grouped by models. Again with an example:
source target model units
------ ------ ----- -----
1 2 1 5 -- 5 sent from 1 to 2
1 2 2 1
2 1 1 2 -- 2 sent from 2 to 1
2 1 1 1 -- 1 more sent from 2 to 1
The result should be:
source target model diff
------ ------ ----- ----
1 2 1 2 -- 5 sent minus 3 received
1 2 2 1
I wonder if this is possible with a single SQL query.
Here is the table creation script and some data, just in case anyone wants to try it:
CREATE TEMP TABLE movements
(
source INTEGER,
target INTEGER,
model INTEGER,
units INTEGER
);
insert into movements values (1,2,1,5);
insert into movements values (1,2,2,1);
insert into movements values (2,1,1,2);
insert into movements values (2,1,1,1);
