I have two SQLite tables:
CREATE TABLE T_A (year_A INT, amount_A DOUBLE);
year_A | amount_A
----------------------
2020 | 100.0
2020 | 200.0
2021 | 300.0
2021 | 400.0
CREATE TABLE T_B (year_B INT, amount_B DOUBLE);
year_B | amount_B
----------------------
2021 | 1000.0
2021 | 2000.0
2022 | 3000.0
2022 | 4000.0
I would like a VIEW
with the SUM
of amount_A
and amount_B
on every year present either in T_A
or T_B
:
View Sums
year | SUM(amount_A) | SUM(amount_B)
------------------------------------------
2020 | 300.0 | 0.0
2021 | 700.0 | 3000.0
2022 | 0.0 | 7000.0
If I use an INNER JOIN
in my query, all I get is a result for year
2021.
A FULL OUTER JOIN
is what I need. As it does not exist in SQLite, I tried to apply this workaround. But I cannot get it work properly as a SUM
is involved too.
SQLite supports FULL OUTER JOIN since version 3.39.0. I use SQLite through Python 3.10. It's a pain to upgrade to a newer version.
SQLite supports FULL OUTER JOIN
since version 3.39.0.
First you must aggregate inside each of the tables and then do a FULL
join on the aggregated results:
WITH
cte_A AS (SELECT year_A, SUM(amount_A) AS sum_A FROM T_A GROUP BY year_A),
cte_B AS (SELECT year_B, SUM(amount_B) AS sum_B FROM T_B GROUP BY year_B)
SELECT COALESCE(a.year_A, b.year_B) year,
COALESCE(a.sum_A, 0) AS sum_A,
COALESCE(b.sum_B, 0) AS sum_B
FROM cte_A AS a FULL OUTER JOIN cte_B AS b
ON b.year_B = a.year_A
ORDER BY year;
For previous versions of SQLite use UNION ALL
and then aggregate:
WITH cte AS (
SELECT year_A AS year, amount_A, 0 AS amount_B FROM T_A
UNION ALL
SELECT year_B, 0, amount_B FROM T_B
)
SELECT year,
SUM(amount_A) AS sum_A,
SUM(amount_B) AS sum_B
FROM cte
GROUP BY year
ORDER BY year;
See the demo.