MySQLで指定した年に対して全くレコードがない月も含めて1月から12月まで件数を取得させたい

Question

Mysqlバージョン5.6.26です。

題名の通り、今、指定した年に対して、全ての月（1月～12月まで）の件数を取得させたいと思っています。
つまり、レコードが全くない月は0件として、表示させたいと思っています。

現在のSQL文は、下記の通りです。

SELECT DATE_FORMAT(uit.UserInsertDate, '%Y%m')month,uit.RegistrationCode, count(uit2.RegistrationCode) count 
FROM user_info_table uit LEFT JOIN user_info_table uit2
on uit.RegistrationCode = uit2.RegistrationCode
and uit2.EndFlg = 0 AND uit2.PaymentFlg = 1
where DATE_FORMAT(uit.UserInsertDate, '%Y') = '2010' 
GROUP BY DATE_FORMAT(uit.UserInsertDate, '%Y%m'),uit.RegistrationCode;

現時点での出力は以下の通りですが、これを毎月RegistrationCode1,2,3の合計をcountとして出力させたいと思っています。
+--------+------------------+-------+
| month | RegistrationCode | count |
+--------+------------------+-------+
| 201007 | 1 | 0 |
| 201007 | 2 | 1 |
| 201007 | 3 | 0 |
| 201008 | 3 | 0 |
+--------+------------------+-------+

どうかご教授願います

Comments

[m.ts10806]

コードはマークダウンのcode機能を利用してご提示ください。

[m.ts10806]

レコードがない月のRegistrationCode はどうしますか？

[planet]

修正致しました。慣れないので、申し訳ありません。

[planet]

＞レコードがない月のRegistrationCode はどうしますか？
0件として表示します。

[m.ts10806]

それはcount(uit2.RegistrationCode)の話で、私が確認したいのはuit.RegistrationCodeの件です。

[planet]

uit.RegistrationCodeは全件出力させます。

[m.ts10806]

えっと、でもデータないんですよね？
nullにするとか空にするとか、何かしら固定の情報を表示させるとか
そういうことかと思ってましたが。

[planet]

毎月uit.RegistrationCodeは1と2と3を出力させます。
つまり、つまりレコードは合計「月×uit.RegistrationCode」の36件出力する事になります。

[hihijiji]

今環境がないので案だけです。
あらかじめMonthカラムだけのテーブルを作って1～12を入れておく
同じようにRegistrationCodeだけのテーブルを作って1～3を入れておく
FROM句で、その2つをCROSS JOIN して
count はSELECT句のサブクエリで

[planet]

できればテーブルは作成したくはないのですが、やり方としては手かもしれませんね。

[planet]

少し時間がなくなっているので返事が適当になっているので、あとでじっくりと見てみます

Answer 1

無いデータは取得しようがありませんから、カレンダーテーブルを準備するか、SQLに該当するものを動的に作成します。

以下は動的に生成されたSQLのイメージ

SELECT ClndReg.YM, ClndReg.RegistrationCode, count(uit2.RegistrationCode) count 
FROM (
        (-- 以下がカレンダー
          select '201007' as ym
        union all
          select '201008'
        ) Clnd
      , (-- 以下がRegistrationCodeの固定値(マスターがあるならマスターで）
          select 1 as RegistrationCode
        union all
          select 2
        union all
          select 3
        ) Reg
     ) ClndReg
     left join user_info_table uit 
     on   ClndReg.ym = DATE_FORMAT(uit.UserInsertDate, '%Y%m')
      and ClndReg.RegistrationCode = uit.RegistrationCode
     LEFT JOIN user_info_table uit2
     on   uit.RegistrationCode = uit2.RegistrationCode
      and uit2.EndFlg = 0 AND uit2.PaymentFlg = 1
where ClndReg.YM like '2010%' 
GROUP BY ClndReg.YM, ClndReg.RegistrationCode

Comments

[planet]

わざわざコードありがとうございます。具体的なコードがあるとイメージしやすいです。
コードを見ながらじっくりと考えてみます。

[planet]

返事が遅くなってすみません。コードで実現する事にしましたが、実際に私が考えている事に近い事をコードで書いて頂いたsaziさんをベストアンサーとします。どうもありがとうございました！

Answer 2

procedureでテンポラリテーブルをつくって12月ヶ月分データをつくっておき
right joinすればよいでしょう

Comments

[planet]

一時的にテーブルを作るのですね。動作的にどうかも含めて確認してみます。