Chris Becker (sl*****@gmail.com) writes:
I have the following query:
SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BY
Month
Unfortunately it only returns rows for months that have data assigned
to them.
How can I tweak this so that months 1-12 are returned, and Hits = 0 for
months with no data in the base table?
SELECT a.Month, SUM(h.Hits)
FROM (SELECT 1 UNION SELECT 2 SELECT 3 UNION
SELECT 4 UNION SELECT 5 SELECT 6 UNION
SELECT 7 UNION SELECT 8 SELECT 9 UNION
SELECT 10 UNION SELECT 11 SELECT 12) a
LEFT JOIN tblHits h ON a.Month = h.Month
GROUP BY a.Month
ORDER BY a.Month
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp