Put empty spaces in an SQL select

Question

I'm having difficulty creating a month->count select query in SQL.

Basically, I have a list of entries, all of which have a date associated with them. What I want the end result to be, is a list containing 12 rows (one for each month), and each row would contain the month number (1 for January, 2 for February, etc), and a count of how many entries had that month set as it's date. Something like this:

Month - Count
1     - 12
2     - 0
3     - 7
4     - 0
5     - 9
6     - 0

I can get an result containing months that have a count of higher than 0, but if the month contains no entries, the row isn't created. I get this result just by doing

SELECT Month(goalDate) as monthNumber, count(*) as monthCount 
FROM goalsList 
WHERE Year(goalDate) = 2012 
GROUP BY Month(goalDate) 
ORDER BY monthNumber

Thanks in advance for the help!

will you please able to specify your table structure in details? — Freelancer
– Freelancer, Commented Sep 26, 2012 at 3:23
Why would you expect a result for a row that does not exist - try left joining against a table that has all the months — Adrian Cornish
– Adrian Cornish, Commented Sep 26, 2012 at 3:26

John Woo · Accepted Answer · 2012-09-26 03:36:41Z

2

Try something like this,

SELECT a.monthNo, COUNT(b.goalDate)
FROM (
        SELECT 1 monthNo UNION SELECT 2 monthNo
          UNION
        SELECT 3  monthNo UNION SELECT 4 monthNo
          UNION
        SELECT 5  monthNo UNION SELECT 6 monthNo
          UNION
        SELECT 7  monthNo UNION SELECT 8 monthNo
          UNION
        SELECT 9  monthNo UNION SELECT 10 monthNo
          UNION
        SELECT 11  monthNo UNION SELECT 12 monthNo
      )  a LEFT JOIN goalsList b
            ON a.monthNo = CAST(month(b.goalDate) as SIGNED)
GROUP BY a.monthNo
ORDER BY a.monthNo;

The idea was to create a list of records for month number in a temporary table and joins it with the table against goalsList. (Assuming that the OP doesn't have a table for month numbers)

SQLFiddle Demo

edited Sep 26, 2012 at 3:36

answered Sep 26, 2012 at 3:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nathan gonzalez Over a year ago

i suggest everyone have a numbers table or tvf available for uses like this.

Collectives™ on Stack Overflow

Put empty spaces in an SQL select

1 Answer 1

SQLFiddle Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related