UNION ALL tables with different column names

Question

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?

Answer 1

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.

Answer 2

here is an example on how to do it

SELECT name, secteur, 'dsd_route' as state
FROM dsd_route 
UNION SELECT name,null as secteur, 'dsd_region' as state
FROM dsd_region
union select model as name ,null as secteur, 'ir_model' as state
FROM ir_model

Answer 3

I think you actually want a JOIN here, like this:

SELECT t1.traffic_volume_1, t2.traffic_volume_2,
CASE WHEN t1.traffic_source IS NOT NULL THEN t1.traffic_source
     ELSE t2.traffic_source END AS traffic_source,
CASE WHEN t1.timestamp IS NOT NULL THEN t1.timestamp
     ELSE t2.timestamp END AS timestamp
FROM t1
FULL JOIN t2
ON t1.timestamp = t2.timestamp AND t1.traffic_volume_1 = t2.traffic_volume_2

Here is a simple example:

CREATE TABLE t1 (traffic_volume_1 int, traffic_source varchar(2), timestamp date);
CREATE TABLE t2 (traffic_volume_2 int, traffic_source varchar(2), timestamp date);

INSERT INTO t1 VALUES (500, 'US', '2018-01-01'), (250, 'US', '2018-01-02');
INSERT INTO t2 VALUES (400, 'US', '2018-01-01'), (250, 'US', '2018-01-03');

The query above would give you:

    traffic_volume_1    traffic_volume_2    traffic_source  timestamp
1   500                 NULL                US              01.01.2018
2   250                 NULL                US              02.01.2018
3   NULL                400                 US              01.01.2018
4   NULL                250                 US              03.01.2018

Tested here: http://rextester.com/XNV59775

Answer 4

Add a placeholder for the 0 value columns in both halves of the UNION:

SELECT count(traffic_volume_1) as traffic_volume_1, 
       0 as traffic_volume_2,
       traffic_source, 
       timestamp
FROM table_1
GROUP BY traffic_source, 
         timestamp
UNION ALL 
       0 as traffic_volume_1,
       count(traffic_volume_2) as traffic_volume_2, 
       traffic_source, 
       timestamp
FROM table_2
GROUP BY traffic_source, 
         timestamp

Answer 5

Move the queries to the from clause. You can combine them using full outer join:

SELECT COALESCE(t1.traffic_source, t2.traffic_source) as traffic_source,
       COALESCE(t1.timestamp, t2.timestamp) as timestamp,
       t1.traffic_volume_1, t2.traffic_volume_2
FROM (SELECT count(traffic_volume_1) as traffic_volume_1, traffic_source, timestamp
      FROM table_1
      GROUP BY traffic_source, timestamp
     ) t1 FULL OUTER JOIN
     (SELECT count(traffic_volume_2) as traffic_volume_2, traffic_source, timestamp
      FROM table_2
      GROUP BY traffic_source, timestamp
     ) t2
     ON t1.traffic_source = t2.traffic_source AND t1.timestamp = t2.timestamp