How to fill in the missing months?

Question

I have the following SQL:

SELECT STRFTIME('%m', date) ord, CASE STRFTIME('%m', date) WHEN '01' THEN 'January' WHEN '02' THEN 'Febuary' WHEN '03' THEN 'March' WHEN '04' THEN 'April' WHEN '05' THEN 'May' WHEN '06' THEN 'June' WHEN '07' THEN 'July' WHEN '08' THEN 'August' WHEN '09' THEN 'September' WHEN '10' THEN 'October' WHEN '11' THEN 'November' WHEN '12' THEN 'December' ELSE '' END AS month, count(*) AS count FROM events WHERE type='Birth' AND date <> '' GROUP BY month,ord ORDER BY ord 

This gives me results similar to:

 ord month count 01 January 1 02 Febuary 1 03 March 3 05 May 4 07 July 2 08 August 2 09 September 2 11 November 4 

But, as you see, he has gaps. Is there a way to fill in the missing months with the number 0?

Answer 1

Learn the outer connection.

Perform one of the following actions:

 CREATE TABLE months ( mm char(2), name char(10) ); INSERT INTO months VALUES ('01', 'January'); INSERT INTO months VALUES ('02', 'Febuary'); INSERT INTO months VALUES ('03', 'March'); INSERT INTO months VALUES ('04', 'April'); INSERT INTO months VALUES ('05', 'May'); INSERT INTO months VALUES ('06', 'June'); INSERT INTO months VALUES ('07', 'July'); INSERT INTO months VALUES ('08', 'August'); INSERT INTO months VALUES ('09', 'September'); INSERT INTO months VALUES ('10', 'October'); INSERT INTO months VALUES ('11', 'November'); INSERT INTO months VALUES ('12', 'December'); 

And run this:

  SELECT m.mm AS ord, m.name AS month, count(e.date) AS count FROM months m LEFT OUTER JOIN events e ON strftime('%m', e.date) = m.mm WHERE e.type='Birth' AND e.date <> '' GROUP BY month,ord ORDER BY ord;