MySQLにて別々に集計した結果を横並びで取得したい

Question

質問投稿機能を作成している者です。
環境はMySQLを使っており、以下のような構成のテーブルがあります。

select * from questions;

questionID
1
2
3

select * from answerTable;

answerID	questionID
1	1
2	2
3	2
4	3
5	3
6	3

select * from questionReaction

reactionID	questionID	reaction
1	1	0
2	1	1
3	2	0
4	2	0
5	2	1
6	3	1
7	3	1
8	3	0

ここで

select questionID  , sum(reaction=0) as goodCount  , sum(reaction=1) as badCount 
 from questionReaction group by questionID ;

とすると

questionID	goodCount	badCount
1	1	1
2	2	1
3	1	2

が得られました(この結果をAとします)。さらに

select questionID, count(questionID) as answerValue from answerTable group by questionID;

とすると

questionID	answerValue
1	1
2	2
3	3

が得られました(この結果をBとします)。ここでやりたいことが、AとBを結合し、

questionID	answerValue	goodCount	badCount
1	1	1	1
2	2	2	1
3	3	1	2

と表示したいです。大変長くなってしまい申し訳ありません。よろしくお願いいたします。

Answer 1

テーブルごとに集計したものを結合します。

select Q.questionID
     , coalesce(A.answerValue, 0) as answerValue
     , coalesce(Q.goodCount, 0) as goodCount
     , coalesce(Q.badCount, 0) as badCount
from  (
        select questionID, sum(reaction=0) as goodCount, sum(reaction=1) as badCount 
        from  questionReaction 
        group by questionID
      ) Q left join (
        select questionID, count(questionID) as answerValue 
        from  answerTable 
        group by questionID
      ) A
      on Q.questionID=A.questionID

Answer 2

2つSQLの結果をUNIONしてみました。questionsも加えました

select questionID,
       sum(answerValue) answerValue,
       sum(goodCount) goodCount,
       sum(badCount) badCount
from (select questionID ,0 as answerValue, sum(reaction=0) as goodCount,sum(reaction=1) as badCount
        from questionReaction group by questionID
      union all
      select questionID, count(questionID) as answerValue, 0 , 0 from answerTable group by questionID
      union all
      select questionID, 0, 0, 0 from questions
) tbl1
join questions using(questionID)
group by questionID order by questionID;

Answer 3

質問のテーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに訂正できたほうがSQLを実行できるので的確なコメントが付き易いかと。

select QR.questionID
     , count(QR.questionID) as answerValue
     , sum(QR.reaction=0) as goodCount
     , sum(QR.reaction=1) as badCount
from questionReaction QR
inner join answerTable AT
on QR.questionID = AT.questionID
group by QR.questionID ;

Comments

[sazi]

結合しているから件数が変わります。それを考慮しないと。
また、innerしているというのもanswerが無い場合にreactionnoの件数が出ませんし。

[Orlofsky]

忘れていました。
質問者はCREATE TABLEやINSERTを追加する気配がないのは新型コロナウイルスがらみではないことを祈ります。

27日午前10時半の時点で日本国内で感染が確認された人は、... 合わせて894人。このうち死亡したのは...7人。
https://www3.nhk.or.jp/news/html/20200227/k10012303921000.html