[MySQL] 月ごとのカウント抽出で歯抜けになっている月を埋める

やりたいこと

月ごとのレコード数のカウントを取りたいが、レコードのない月でもカウント0で出力したい
日付マスタみたいなテーブルは作りたくない。SQLだけで完結させたい

対象のテーブル

データ

usersテーブル

created_at
2020-09-01
2020-09-01
2020-07-01
2020-07-01
2020-05-01

普通にSELECTした場合の出力

月	カウント
202009	2
202007	2
202005	1

以下のような出力が欲しい

月	カウント
202009	2
202008	0
202007	2
202006	0
202005	1

歯抜け対応のSQL

月一覧を出力する文を作成して、UNION ALLすることで対応する

SELECT
    EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(), INTERVAL(months.series) MONTH )) AS yearMonth
  FROM (
    SELECT 0 AS series
    FROM DUAL
    WHERE (@num := 1) * 0
    UNION ALL
    SELECT @num := @num - 1
    FROM information_schema.COLUMNS
    LIMIT 12
  ) AS months

最終的なSQLはこちら

• 月一覧取得の方に0 AS count を追加

SELECT
  s.yearMonth AS yearMonth,
  SUM(s.count) AS count
FROM (
  SELECT
    EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(), INTERVAL(months.series) MONTH )) AS yearMonth,
    0 AS count
  FROM (
    SELECT 0 AS series
    FROM DUAL
    WHERE (@num := 1) * 0
    UNION ALL
    SELECT @num := @num - 1
    FROM information_schema.COLUMNS
    LIMIT 12
  ) AS months
  UNION ALL
  SELECT
    EXTRACT(YEAR_MONTH FROM users.created_at) AS yearMonth,
    COUNT(*) AS count
  FROM
    users
  GROUP BY yearMonth
) AS s
GROUP BY s.yearMonth
ORDER BY s.yearMonth DESC
LIMIT 12

追記

MySQL8の場合は、以下のコメントにあるWITH句を使った方法がわかりやすいです！