Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE purchase
(
id SERIAL PRIMARY KEY,
date DATE NOT NULL
)
CREATE TABLE air_transfers
(
id SERIAL PRIMARY KEY,
purchase_id INTEGER NOT NULL REFERENCES purchase(id),
units INTEGER NOT NULL
)
CREATE TABLE sea_transfers
(
id SERIAL PRIMARY KEY,
purchase_id INTEGER NOT NULL REFERENCES purchase(id),
units INTEGER NOT NULL
)
INSERT INTO Purchase(date) VALUES('02/10/2022')
1 rows affected

INSERT INTO air_transfers(purchase_id,units) VALUES(1,500)
1 rows affected

WITH sea_transfers AS (
SELECT purchase_id, SUM(units) AS units
FROM sea_transfers
GROUP BY purchase_id
),
air_transfers AS (
SELECT purchase_id, SUM(units) AS units
FROM air_transfers
GROUP BY purchase_id
)
SELECT st.purchase_id, (COALESCE(st.units,0) + COALESCE(at.units,0)) AS units
FROM sea_transfers AS st
FULL OUTER JOIN air_transfers AS at
ON st.purchase_id = at.purchase_id


purchase_id
units


null
500


INSERT INTO sea_transfers(purchase_id,units) VALUES(1,300)
1 rows affected

WITH sea_transfers AS (
SELECT purchase_id, SUM(units) AS units
FROM sea_transfers
GROUP BY purchase_id
),
air_transfers AS (
SELECT purchase_id, SUM(units) AS units
FROM air_transfers
GROUP BY purchase_id
)
SELECT st.purchase_id, (COALESCE(st.units,0) + COALESCE(at.units,0)) AS units
FROM sea_transfers AS st
FULL OUTER JOIN air_transfers AS at
ON st.purchase_id = at.purchase_id


purchase_id
units


1
800