By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,011 Members | 1,000 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,011 IT Pros & Developers. It's quick & easy.

Select data from table an return all all months

P: n/a
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?

Thanks.

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try this:

SELECT Month, COALESCE(SUM(Hits),0) AS Hits
FROM (
SELECT 1 AS Month
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
) T1
LEFT JOIN Hits
ON Hits.Month=T1.Month
GROUP BY T1.Month
ORDER BY T1.Month

The query is a bit easier if you use a numbers table instead of the
current subquery.

Gert-Jan
Chris Becker wrote:

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?

Thanks.

Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.