「仕事」と「申し込み」の以下のようなテーブルを結合して
▼tb_job(仕事)
|job_id|name|
|1|漁師|
|2|医者|
|3|農家|
|4|警官|
▼tb_contact(申し込み)
|contact_id|job_id|user|
|1|2|佐藤|
|2|2|田中|
|3|1|本村|
各仕事の申込数を以下のように出力したいと思っています。
▼理想
|job_id|name|申込数|
|1|漁師|1|
|2|医者|2|
|3|農家|0|
|4|警官|0|
そこで上記2つ「tb_contact」を「job_id」でGROUP BYして
SQLで以下のように結合するのですが
SELECT *,COUNT(*) FROM tb_contact RIGHT JOIN tb_job ON tb_contact.job_id = tb_job.job_id GROUP BY tb_contact.job_id;
下記のように申し込みがある仕事については集計できるのですが、
申し込みがない仕事については1括りにされてしまい、COUNTにも
その合計が表示されてしまいます。
▼結果
|contact_id|job_id|user|job_id|name|COUNT(*)
|NULL|NULL|NULL|3|農家|2
|3|1|本村|1|漁師|1
|1|2|佐藤|2|医者|2
「▼理想」のように出力するにはどの様なSQL文を投げれば良いのでしょうか。
ご教授のほど宜しくお願い致します。
とりあえず、一番の原因はgroup by に 「tb_contactの」job_idを指定していることです。
tb_contactのjob_idに、3や4の値はないわけで、right joinした結果も、その値は当然nullです。
その結果「group by の値はnull」として、一列になってしまいます。
ということで、元のSQL文への変更を最小限にするなら
SELECT *,COUNT(*) FROM tb_contact RIGHT JOIN tb_job ON tb_contact.job_id = tb_job.job_id GROUP BY tb_job.job_id;
となります。
補足として、
group by または集計関数 を使用する時、Selectに「group by に指定されていない集計関数以外の列」を指定できてしまうのはMYSQLの独自拡張です。
今回も最初のSQLが「通ってしまう」ことが誤解の一員になっているんじゃないかと思います。
参考:寛容なMySQLを非寛容にすること(その3) | inquisitor
それをふまえた上で「理想」と同じ出力にするなら、こんな感じで。
SELECT tb_job.job_id,tb_job.name,COUNT(tb_contact.job_id) AS `申込数` FROM tb_contact RIGHT JOIN tb_job ON tb_contact.job_id = tb_job.job_id GROUP BY tb_job.job_id,tb_job.name;
ちなみに、私の場合、
というのを習慣にしているので、私が書くならこんな感じになります。
SELECT A.job_id,A.name,COUNT(B.job_id) AS `申込数` FROM tbl_job AS A LEFT JOIN tbl_contact AS B ON A.job_id=B.job_id GROUP by A.job_id,A.name;
2例ほど・・・
データの量や内容によって速度が逆転したりするので、
実データで比較して選択する必要があります
(下記は、動作確認せず、いきなり書いています・・・)
(a)
SELECT a.job_id, a.name , (SELECT COUNT(*) FROM tb_contact b WHERE b.job_id = a.job_id) AS `申込数` FROM tb_job a ;
(b)
SELECT a.job_id, a.name, COUNT(*) AS `申込数` FROM tb_job a LEFT JOIN tb_contact b ON b.job_id = a.job_id GROUP BY a.job_id, a.name ;
とりあえず無事解決出来ました。詳しい内容についてはまた後ほどじっくり拝見させて頂きます。
ありがとうございました。
7:20ごろ起きて、回答入ってなかったから書いてあげようとしたらわずか10分くらいの間に・・・ちょっとびっくりした。