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.
3
-
1I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using.user330315– user3303152021-06-08 05:27:10 +00:00Commented Jun 8, 2021 at 5:27
-
When you say "count of ids" do you mean "count of distinct ids"? In other words, should the result contain (A, 3, 2) or (A, 2, 2)?Branko Dimitrijevic– Branko Dimitrijevic2021-06-08 06:13:13 +00:00Commented Jun 8, 2021 at 6:13
-
Tag your question with the database you are using!Gordon Linoff– Gordon Linoff2021-06-08 12:44:34 +00:00Commented Jun 8, 2021 at 12:44
Add a comment
|
3 Answers
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.
1 Comment
Tim Biegeleisen
@gocode Please check the explanation given at the end of the answer.
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.
1 Comment
Gordon Linoff
@gocode . . . Needed for what? If you have a different question, then ask a new question with appropriate sample data, desired results, and explanation.
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 |
answered Jun 8, 2021 at 6:25
Branko Dimitrijevic
52.3k12 gold badges104 silver badges174 bronze badges
Comments
