0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

WITH Questions AS (
SELECT YEAR(CreationDate) AS [Year], COUNT(*) AS Total
  FROM Posts
  WHERE PostTypeId = 1
  GROUP BY YEAR(CreationDate)
), Answers AS (
SELECT YEAR(CreationDate) AS [Year], COUNT(*) AS Total
  FROM Posts
  WHERE PostTypeId = 2
  GROUP BY YEAR(CreationDate)
)
SELECT COALESCE(q.Year, a.Year) AS [Year],
  COALESCE(q.Total, 0) AS Questions,
  COALESCE(a.Total, 0) AS Answers
  FROM Questions AS q
  FULL OUTER JOIN Answers AS a
    ON q.Year = a.Year
  ORDER BY 1

fork query

edited nov 30 23 anonymous

created apr 6 22 Glorfindel