Join results "generated" from two SELECT statements with different schemas into one table
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 aINNER 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 bON a.decade = b.decade