470,591 Members | 2,283 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Select data from table an return all all months

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
2 7781
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
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.

Similar topics

2 posts views Thread by JohnnyOnTheSpot | last post: by
4 posts views Thread by jimh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.