SQL: Union with subqueries

Question

My structure is like this

table1 key value date

table2 key value date

table3 key value date

I would like to have a sql query which returns distinct all keys and the sum of all values for each key from all 3 tables.

My try has been

Select key=
(select table1.key from table1 where date = '2017-05-30' union
select table2.key from table2 where date = '2017-05-30' union
select table3.key from table3 where date = '2017-05-30'),
(select sum(value) from table1 where table1.key = key and date = '2017-05-30' ) +
select (sum(value) from table2 where table2.key = key and date = '2017-05-30') +
select (sum(value) from table3 where table3.key=key and date = '2017-05-30')
from table1, table2, table3

please format query - it is barely readable

Vao Tsun
– Vao Tsun

2017-06-01 10:32:40 +00:00
Commented Jun 1, 2017 at 10:32 — Vao Tsun
– Vao Tsun, Commented Jun 1, 2017 at 10:32

Ryan · Accepted Answer · 2017-06-01 10:38:04Z

You could combine the 3 tables into a temporary table and then do what you need to do on the temporary table:

CREATE TEMP TABLE temp1 AS select key, value, date from table1 union all select key, value, date from table2 union all select key, value, date from table3

Collectives™ on Stack Overflow

SQL: Union with subqueries

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related