MySQL 条件つきでCOUNT、SUM、AVGする

SELECT分で、WHEREで条件を指定して取り出したデータを、COUNTしたり、SUM(合計)したり、AVG(平均)したりする方法。

まずは、サンプルデータを作ります。

ここから、ageの数、合計、平均を取得します。

つぎに条件付きで取得する場合。以下の例では、30才以上の数、合計、平均を取得します。

COUNTは単にCOUNT(age>=30)だと、ちゃんと数えてくれません。なんでも、COUNTは()内の結果が、TRUEでもFALSEでも1としてカウントしてしまうからだそうです。
つまり、TRUEかNULLかでカウントしなければならんということです。

TRUE → 1
FALSE → 1

ということで、 OR NULLをつけるとうまくいくというけれど、なんで?
というのを、検証してくださっている方がいて、結論から言うと、FALSEをNULLに変換しているわけですが、そのロジックはというと、

TRUE OR NULL → TRUE → 1
FALSE OR NULL → NULL → 0

となるからだそうです。

というわけで、 OR NULL とすることで、TRUEのときにはTRUE、FALSEのときにはNULLとなって、COUNT(age>=30 OR NULL) でちゃんとうまくいくと。

つぎにSUM()ですが、CASE WHEN文を使えばいいそうです。
ちなみに、SUM(age>=30 OR NULL) とやると、COUNTと同じ結果がかえってきます。TRUEなら1ってことで1×数なので、そりゃそうか。

つぎに平均ですが、AVG()にもCASE WHEN文は使えますが、平均する母数はそのままなので、「30才以上の平均」とはなりません。サンプルデータだと、(31+30+31)÷6 となってしまいますので、SUMとCOUNTの組み合わせで平均を計算します。

ちなみに、OR NULL や、CASEを使わずに、IFを使ってもできます。こんな感じで。

以上、取得したデータから条件で数、合計、平均を取り出せますが、実際、この程度のデータと条件であれば、WHERE句で age>=30 とすればいいのですが、たとえば、30以上と、30未満の合計などを一度に取得したいときは、スマートに記述できていいです。こんな感じで。

参考になったサイトさん:
COUNT:https://www.softel.co.jp/blogs/tech/archives/3267
SUM:http://d.hatena.ne.jp/deeeki/20090521/sumcasewhen
AVG:https://blogs.yahoo.co.jp/warp_green_3/23908557.html

タイトルとURLをコピーしました