Appointments per month, December missing?

Question

SELECT month(dateofappointment), COUNT(*) 'NumberOfAppointments'
    FROM appointment 
    WHERE YEAR(dateofappointment) = '2016'  
    GROUP BY  MONTH(dateofappointment) 

this shows me all months but December is not there because there weren't any appointments made in that year. how do i show December as being 0?

Answer 1

To solve these types of queries it often helps to express them as a series of requirements, this can make it easier to resolve.

When the results don't come out as expected, update your requirements statements with new requirements as you identify them, then try again:

As I see it now you have 2 requirements:

1. Return a single row for each month of the year of 2016
2. For each row show a count of the appointments for the corresponding month

Ok so that was verbose, but you see what you are missing from your query is a statement that defines the '1 row for each month of the year 2016' So you need to build that recordset first, either manually or through recursion.

MySQL does not currently support recursive Common Table Expressions, this is a trivial concept in many other RDBMSs

But if MySQL doesn't support recursion, what are our options? Here are some other attempts on SO:

• МуSQL Get a list of dates in month, year
• How to generate a dynamic sequence table in MySQL?
• generate an integer sequence in MySQL

This might sound a bit of a hack, but you can use any table in your database that has more than 12 rows and has an auto-incrementing field, oh and was seeded to start at 1 (or below). Forget about whether this is right or wrong, it will work:

SELECT Id 
FROM LogEvent -- An arbitrary table that I know has records starting from 1
WHERE Id BETWEEN 1 AND 12

So that is hacky, but we can implement a row count function so that we can use any table with 12 or more rows, regardless of ids or seeding, stole this from: MySQL get row number on select - Answer by Mike Cialowicz

SET @rank=0;
SELECT @rank:=@rank+1 AS rank
FROM orders
WHERE rank <= 12

Now we can either union the missing rows from this result set to the original query or use a join operator. First solution using union.

It is common to use UNION ALL to inject missing rows to a recordset because it separates the expected result query from the exceptional or default results. Sometimes this syntax makes it easier to interpret the expected operation

SET @rank = 0;

SELECT month(dateofappointment) as Month, COUNT(*) 'NumberOfAppointments'
FROM appointment 
WHERE YEAR(dateofappointment) = '2016'  
GROUP BY  MONTH(dateofappointment) 

UNION ALL

SELECT rank, 0
FROM (
    SELECT @rank:=@rank+1 AS rank
    FROM rows
    WHERE @rank < 12
) months
WHERE NOT EXISTS (SELECT dateofappointment 
                  FROM appointment
                  WHERE YEAR(dateofappointment) = '2016' AND MONTH(dateofappointment) = months.rank)
ORDER BY Month

But it makes for an ugly query. You could also join on the months query with a left join on the count of appointments, but here the intention is harder to identify.

SET @rank = 0;

SELECT months.rank, COUNT(appointment.dateofappointment) 
FROM (
    SELECT @rank:=@rank+1 AS rank
    FROM rows
    WHERE @rank < 12
) months
LEFT OUTER JOIN appointment ON months.rank = Month(appointment.dateofappointment) AND YEAR(dateofappointment) = '2016'
GROUP BY months.rank

I have saved these queries into a SqlFiddle so you can see the results: http://sqlfiddle.com/#!9/99d485/4

As I pointed out above, this is trivial in MS SQL and Oracle RDBMS, where we can generate sequences of values dynamically through recursive Common Table Expressions (CTEs) For the players at home here is an implementation in MS SQL Server 2014. The example is a little more evolved, using a from and to date to filter the results dynamically

-- Dynamic MS SQL Example using recursive CTE
DECLARE @FromDate Date = '2016-01-01'
DECLARE @ToDate Date = '2016-12-31'
;
WITH Months(Year, Month, Date) AS
(
    SELECT Year(@FromDate), Month(@FromDate), @FromDate
    UNION ALL
    SELECT Year(NextMonth.Date), Month(NextMonth.Date), NextMonth.Date
    FROM Months
    CROSS APPLY (SELECT DateAdd(m, 1, Date) Date) NextMonth
    WHERE NextMonth.Date < @ToDate
)
SELECT Months.Year, Months.Month, COUNT(*) as 'NumberOfAppointments'
FROM Months
LEFT OUTER JOIN appointment ON Year(dateofappointment) = Months.Year AND Month(dateofappointment) = Months.Month
GROUP BY Months.Year, Months.Month