CODEWITHSUNDEEP
postgresql
gocode
gocode

Reputation: 375

SQL query join from table 1 and table 2

Given two tables t1 and t2 with the same column names:

places, visits, and types_of_events

The output table should have places in the first column. The second column should have types_of_events from t2.

Upvotes: 1

Views: 376

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would use union all, but with this twist:

select var1, sum(cnt1), sum(cnt2)
from ((select var1, count(*) as cnt1, 0 as cnt2
       from t1
       group by var1
      ) union all
      (select var1, 0, count(*)
       from t2
       group by var1
      )
     ) t
group by var1;

The idea is that by repeating columns, you can avoid the case in the outer query. You can also write this using full join. In Standard SQL, this looks like:

select *
from (select var1, count(*) as cnt1, 0 as cnt2
      from t1
      group by var1
     ) t1 full join
     (select var1, 0, count(*)
      from t2
      group by var1
     )
     using (var1);

However, not all databases support this syntax.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

This gives you the number of distinct IDs (per table) associated to each unique var1:

SELECT
    COALESCE(t1.var1, t2.var1) var1,
    COUNT(DISTINCT t1.id) count1,
    COUNT(DISTINCT t2.id) count2
FROM
    t1
    FULL JOIN t2
        ON t1.var1 = t2.var1
GROUP BY
    COALESCE(t1.var1, t2.var1);

Result:

var1 count1 count2
A 2 2
B 1 4
C 2 1
D 0 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use a union along with a computed column to keep track of the table source:

SELECT
    var1,
    COUNT(CASE WHEN src = 1 THEN 1 END) AS cnt_1,
    COUNT(CASE WHEN src = 2 THEN 1 END) AS cnt_2
FROM
(
    SELECT var1, id, 1 AS src FROM t1
    UNION ALL
    SELECT var1, id, 2 FROM t2
) t
GROUP BY
    var1;

For an explanation, the inner union query brings together the two data sets, however introducing an src column which is either 1 or 2, corresponding to the table source. Then, in the outer query, we aggregate by var1 and take separate conditional counts of the ids for each table.

Upvotes: 1

Related Questions