I have two tables that are similar and I am trying to join them in a Union and then probably group by. I am looking to add a column of null or 0 where the tables do not overlap.
SELECT count(traffic_volume_1) as traffic_volume_1,
traffic_source,
timestamp
FROM table_1
UNION ALL
count(traffic_volume_2) as traffic_volume_2,
traffic_source,
timestamp
FROM table_2
...?
I am looking for a return that would look like:
traffic_volume_1, traffic_volume_2, timestamp, traffic_source
77777 , 0 , 2018-02-09, US
0 , 928320 , 2018-02-09, EU
Any ideas?
If you know the number of extra columns you need before you run the query, the answer is straightforward : you don't want a UNION, instead execute a query against each table in a separate clause in the FROM and JOIN them on traffic_source and timestamp.
However, if you don't know how many columns you will have until you run the query then what you need is a crosstab or pivot query.
Pivot queries convert distinct row values into extra columns. You can think of it visually as a rotation of your query recordset by 90 degrees; instead of generating new rows, the engine generates new columns.
Pivot query syntax is platform specific in SQL as it is non-standard. Not sure what platform you are using, but check for support for crosstab/pivot queries.