「group by に含めてください。」とエラーの出る方へ

集計系のクエリではjoinなど複雑なクエリになることもあります。
何とかクエリを作り上げ、いざ実行となったとき

「group by に含めてください。」

というエラーが出るとガッカリします。

そして、このエラーを消すためにgroup byに列を含めると
今度は思った集計結果にならないというジレンマに陥ります。

あくまで経験則にはなりますが、
私まわりでは一度はこのジレンマにはまっているようでした。

原因は、次の２つのことを同時に実行している事だと考えています。

• 集計したい
• 集計結果に付属した表示項目を出したい

そこで今回は「group by に含めてください。」の
エラーを出さないクエリの作り方を2種類紹介したいと思います。

集計と表示項目を分けてみる

まずは「group by に含めてください。」が
どういうときに起きるのかを確認しましょう。

使う表はこちら

この表から

• ユーザID
• ユーザーごとの金額の合計
• ユーザのメールアドレス

を出力するクエリを考えます。

「ユーザーごと」の金額の合計ですからgroup by には user_idが入りますね。

selectには合計金額とメールがあるので、それぞれ入れてあげると、、、

NGです。

group byにmailが入っていないので「group by に含めてください。」と
エラーがでます。

ではmailを入れてみましょう。

もちろん、これもNGです。

出力結果はこちら

エラーは出力されない代わりに、
ユーザーIDが「1」のレコードが複数になっていますよね。

「group by 」にmailを加えたことが原因です。

これにより、「ユーザーごと」の金額ではなく
「ユーザーとメールアドレスごと」の集計になりました。

集計がうまくいかずレコードが増えているこの状態の原因こそ、
集計と表示項目を同時に出力しようとしているからなのです。

解決するには集計と表示項目を分けて考えます。

表示項目を出力するクエリは別のテーブルから作る

一つ目の方法が「まって、それ別テーブルに情報ない？」です。

別のテーブルを参照できるIDがあるならば、
そのIDに紐づく情報の中に表示したい項目があるかもしれません。

先ほどの例ではユーザーIDが記載されていました。
ということは、ユーザーテーブルがあり「mail」を
格納している可能性は高いです。

もし、このようなユーザーテーブルがあった場合はぜひ利用してみてください。

試しに利用するクエリを書いてみます。

集計と表示項目を分けるといっておきながら
一つのクエリじゃないかと思われるかもしれません。

解説します。

このクエリはユーザーデータを取得するクエリと

inner joinの中で作られている集計クエリとで構成されています。

ポイントはどちらのクエリも「ユーザごと」に出力されている点です。

それぞれの出力結果を並べるとこのようになります。

この2つのテーブルをinner joinしてあげると

ユーザごとの合計金額をmailと一緒に出力することができました。

これが集計と表示項目を分けるということです。

あなたの手元に「group by に含めてください。」と出ているクエリがあるならば、
他のテーブルを参照できる外部キーがあるか確認してみてください。

この方法が使えるかもしれません。

同じテーブルから表示項目を作るクエリを用意する

別テーブルを利用すれば、集計した値と表示したい項目を並べることができました。
次は、一つのテーブルから集計と表示項目を分ける方法を紹介します。

一つのテーブルで完結できるのであれば別テーブルの方法は
要らないじゃないかと思われるかもしれません。

なぜ別テーブルの例から紹介したかというと、
一つのテーブルだけでクエリを作るほうがが複雑だからです。

しかしながら、実際の問題として
複雑なクエリを使用しなければならない場面があります。

つまりは、同じデータベース内にユーザーテーブルが
あるとは限りらないということです。

メンテナンスの観点で別データベースに分けていたり、
ユーザー情報を保存するのに別のWEBサービスを利用していることは
十分あり得る話です。

そうなると先ほど紹介した別のテーブルとの結合が出来なくなってしまいます。

そこで、一つのテーブルから
「集計するためのクエリ」「表示項目のためのクエリ」のふたつを作ります。

例として先ほどと同じ注文テーブルを使います。条件も同じです。

• ユーザID
• ユーザーごとの金額の合計
• ユーザのメールアドレス

ポイントは複数の注文をしているユーザーID「１」のレコードを一つに絞り込めるかです。

レコードを一つに絞るための条件ならば何でもよいので
ここでは「ユーザーごとの最新注文日」のレコードを使用することを考えます。

まずは全体のクエリです。

「ユーザーごとの最新」を担っているクエリは以下の部分です。

これをmailを出力しているクエリ

にinner joinします。

すると最新日注文日が「2020/08/01」のユーザーID「１」のレコードだけを
出力対象にすることができます。

これに集計結果を結合させると

ユーザーテーブルと結合させたときと同様の結果になります。

まとめ

２つのシーンを想定してクエリを紹介しました。
これで「group by に含めてください。」とはおさらばです。

何度も繰り返しにはなりますが、
ポイントは項目表示と集計結果を分けることです。

是非、参考にしていただけたら嬉しいです。