【MySQL】countで０件のデータも含めて表示したい

Question

前提・実現したいこと

MySQLでcount０件のデータも含めて表示したい。

テストの問題番号とそれに紐付く点数を表示したanswersテーブルがあります。

id	question_id	score
1	1	9
2	1	9
3	1	10
4	3	1
5	3	1
6	3	5
7	1	6
8	3	8
9	2	2

select question_id,count(*)
from answers
where score in (9,10)
group by question_id;

scoreが9点以上のquestion_idとそのscoreを集計し、scoreが9点以上に該当しないquestion_idには明示的に0またはNULLを表示したい

実現したいSQL結果

question_id	count(*)
1	28
2	0 or NULL
3	0 or NULL

発生している問題・エラーメッセージ

しかし、実際にはこのSQLの結果が下記のようになってしまいます。

question_id	count(*)
1	28

色々と調べてみましたが、解決に至るようなヒントが得られなかったため、
countが０の時も表示されるようにするSQLをお分かりの方がおりましたらご教授いただけたら幸いです。

Answer 1

scoreは集計なんですから、count()ではなくsum()です。
出したい結果は9点以上でないとscoreなしという事なので、caseでそのような条件のものをsum()します。

select question_id, sum(case when score>=9 then score end)
from answers
group by question_id

Comments

[Sakamotos]

ご回答ありがとうございます。こちらのSQL文で実現できました。
こんなに簡単なSQL文で実現できるのはびっくりしました。今後case文も使いこなせるようにしたいと思います。

Answer 2

scoreが9点以上の場合はそのままscoreの値、そうでない場合は0とするカラムを返すサブクエリを作り、それをquestion_idごとにsum()関数で集計するクエリを作れば良いです。
case文を使用します。case文はとても便利で、様々な応用が効きます。

mysql8.0以上限定

with q1 as (
    select
        id
        ,question_id
        ,case when score >= 9 then score else 0 end as target -- ここが重要
    from answers
)
select
    question_id,
    sum(target) as cnt
from q1
group by question_id
order by question_id;

それ以前のバージョンはwith句が使えないのでサブクエリにする。

select
    question_id,
    sum(target) as cnt
from (
    select
        id
        ,question_id
        ,case when score >= 9 then score else 0 end as target
    from answers
) as q1
group by question_id
order by question_id;

Comments

[Sakamotos]

ご回答ありがとうございます。こちらのSQL文で実現できました。
with句、case文でこのように柔軟に対応できるのは勉強になりました。
ありがとうございます。