SQLで回答数の多い質問が属するタグ毎に、回答数の多い順番に抽出したい

Question

Laravel6.3にて開発中のサイトのSQLの組み立てとクエリビルダを構築するのに詰まってしまい、手が止まってしまいました。
表題からはわかり辛いかもしれませんが、抽出したい条件は、質問に対する回答結果を格納しており、質問毎に回答数を集計して、質問に関連するタグ毎に集計した回答数を合計して、合計数の多いタグ毎に並べ替えたい、というものです。

---

簡易ですがテーブル状況は以下です。（不要なカラムは省略しています。）

・質問(文字)テーブル (words)
id int

・タグテーブル (tags)
id int

・タグ質問関連付けテーブル（tag_word）
id int
word_id int (words.id 参照制約)
tag_id int (tags.id 参照制約)

・回答結果テーブル (results)
id int
word_id int (words.id 参照制約)
result tinyint (0か1)

---

回答結果テーブルを文字ID毎に集計し、それをタグ質問関連付けテーブルと結合してタグID毎に集計数を合計させるために以下のようなビルダを書きましたが、どうも最初の集計結果$query->get()->sum('results_count')と結合後の合計数$items->sum('results_sum')が合わなくなって（数が減って）しまっており、正しい結果が得られません。

        $query = \DB::table('results')
            ->selectRaw('word_id, count(*) as results_count')
            ->groupBy('word_id');

//         dd($query->get()->sum('results_count'));

        $items = \DB::table('tag_word')
            ->selectRaw('tag_word.tag_id, sum(words_with_results_count.results_count) as results_sum')
            ->joinSub($query, 'words_with_results_count', 'tag_word.word_id', '=', 'words_with_results_count.word_id')
            ->groupBy('tag_word.tag_id')
            ->orderBy('results_sum', 'desc')
            ->get();
//             ->toArray()
//             ->toSql();

        dd($items->sum('results_sum'));

SQLは以下のように生成されています。
結合方法を間違えているのか、グルーピング方法が違うのか、初歩的な事かもしれませんが、
お詳しい方がおられましたら、御教示頂けると幸いでございます。
どうぞ宜しくお願い致します。

select tag_word.tag_id, sum(words_with_results_count.results_count) as results_sum
from `tag_word` inner join (
  select word_id, count(*) as results_count
  from `results`
  group by `word_id`
) as `words_with_results_count`
on `tag_word`.`word_id` = `words_with_results_count`.`word_id`
group by `tag_word`.`tag_id`
order by `results_sum` desc;

Answer 1

一つのwordに対して、複数のtagが付くなら、同じ回答が別々のtagの合計に含まれるから、tag全体の合計とresultの合計は　tag　>=　result　になります。

合わせるなら、wordに対してtagは一つだけにする必要がありますね。

Comments

[wonda]

ありがとうございます。
仰る通りですね。
また、頂いた回答ををみてふと思ったのですが、自分で多対多の設計をしていながら完全に失念していたのですが、当然ながらタグが関連付かない文字も存在しており、テストデータはランダムで数百件登録していたので、タグ無しの文字に回答があっても集計対象外になりますよね。
結果テーブルの総件数から集計対象外の件数を引いたら、対象の件数が一致しました。
初歩的な事で誠に申し訳ないです。
ありがとうございました。