Join results "generated" from two SELECT statements with different schemas into one table
13,234
Put them in subqueries and do a JOIN
:
SELECT a.US, a.decade, b.total FROM
(SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4) AS Decade
FROM Movies M, Awards A
WHERE {SOME WHERE CLAUSE}
GROUP BY Decade ) AS a
INNER JOIN
(SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade
FROM Awards A2
WHERE {SOME WHERE CLAUSE}
GROUP BY Decade) AS b
ON a.decade = b.decade
Author by
Martin Marinov
Updated on July 09, 2022Comments
-
Martin Marinov almost 2 years
So I have the following SELECT statements:
SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4) AS Decade FROM Movies M, Awards A WHERE {SOME WHERE CLAUSE} GROUP BY Decade;
and
SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade FROM Awards A2 WHERE {SOME WHERE CLAUSE} GROUP BY Decade;
The first one is "generating" a table with columns (US, Decade) and the second one is "generating" another table with columns (Total, Decade). I want to join those two tables so that I get a table (US, Total, Decade). How can I do it?