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

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

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

CREATE TABLE test ( id BIGINT , name TEXT , age SMALLINT ) ;
INSERT INTO test (id, name, age) VALUES ('1', 'nagatomo', '31');
INSERT INTO test (id, name, age) VALUES ('2', 'yoshida', '28');
INSERT INTO test (id, name, age) VALUES ('3', 'honda', '30');
INSERT INTO test (id, name, age) VALUES ('4', 'okazaki', '31');
INSERT INTO test (id, name, age) VALUES ('5', 'kagaawa', '28');
INSERT INTO test (id, name, age) VALUES ('6', 'usami', '25');

CREATE TABLE test ( id BIGINT , name TEXT , age SMALLINT ) ;

INSERT INTO test (id, name, age) VALUES ('1', 'nagatomo', '31');

INSERT INTO test (id, name, age) VALUES ('2', 'yoshida', '28');

INSERT INTO test (id, name, age) VALUES ('3', 'honda', '30');

INSERT INTO test (id, name, age) VALUES ('4', 'okazaki', '31');

INSERT INTO test (id, name, age) VALUES ('5', 'kagaawa', '28');

INSERT INTO test (id, name, age) VALUES ('6', 'usami', '25');

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

/*全数*/
SELECT COUNT(*) FROM test;

/*全合計*/
SELECT SUM(age) FROM test;

/*全平均*/
SELECT AVG(age) FROM test;

/*全数*/

SELECT COUNT(*) FROM test;

/*全合計*/

SELECT SUM(age) FROM test;

/*全平均*/

SELECT AVG(age) FROM test;

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

/*30才以上の人数*/
SELECT COUNT(age>=30 OR NULL) FROM test;

/*30才以上の合計*/
SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) FROM test;

/*30才以上の平均*/
SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END)/COUNT(age>=30 OR NULL) FROM test;

/*30才以上の人数*/

SELECT COUNT(age>=30 OR NULL) FROM test;

/*30才以上の合計*/

SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) FROM test;

/*30才以上の平均*/

SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END)/COUNT(age>=30 OR NULL) FROM test;

COUNTは単にCOUNT(age>=30)だと、ちゃんと数えてくれません。なんでも、COUNTは()内の結果が、TRUEでもFALSEでも１としてカウントしてしまうからだそうです。
つまり、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なら１ってことで１×数なので、そりゃそうか。

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

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

/*30才以上の人数*/
SELECT COUNT(IF( age>=30 , age , null )) FROM test;

/*30才以上の合計*/
SELECT SUM(IF( age>=30 , age , 0 )) FROM test;

/*30才以上の平均*/
SELECT SUM(IF( age>=30 , age , 0 )) / COUNT(age>=30 OR NULL) FROM test;

/*30才以上の平均*/
SELECT SUM(IF( age>=30 , age ,0 )) / COUNT(IF( age>=30 , age , null )) FROM test;

/*30才以上の人数*/

SELECT COUNT(IF( age>=30 , age , null )) FROM test;

/*30才以上の合計*/

SELECT SUM(IF( age>=30 , age , 0 )) FROM test;

/*30才以上の平均*/

SELECT SUM(IF( age>=30 , age , 0 )) / COUNT(age>=30 OR NULL) FROM test;

/*30才以上の平均*/

SELECT SUM(IF( age>=30 , age ,0 )) / COUNT(IF( age>=30 , age , null )) FROM test;

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

/*30才以上と30歳未満のそれぞれの人数*/
SELECT COUNT(age>=30 OR NULL) as above30 , COUNT(age<30 OR NULL) as under30 FROM test;

/*30才以上と30歳未満のそれぞれの合計*/
SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) as above30 ,
	SUM(CASE WHEN age<30 THEN age ELSE 0 END) as under30
	FROM test;

/*30才以上と30歳未満のそれぞれの平均*/
SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) / COUNT(age>=30 OR NULL) as above30 ,
	SUM(CASE WHEN age<30 THEN age ELSE 0 END) / COUNT(age<30 OR NULL) as under30
	FROM test;

/*30才以上と30歳未満のそれぞれの人数*/

SELECT COUNT(age>=30 OR NULL) as above30 , COUNT(age<30 OR NULL) as under30 FROM test;

/*30才以上と30歳未満のそれぞれの合計*/

SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) as above30 ,

SUM(CASE WHEN age<30 THEN age ELSE 0 END) as under30

FROM test;

/*30才以上と30歳未満のそれぞれの平均*/

SELECT SUM(CASE WHEN age>=30 THEN age ELSE 0 END) / COUNT(age>=30 OR NULL) as above30 ,

SUM(CASE WHEN age<30 THEN age ELSE 0 END) / COUNT(age<30 OR NULL) as under30

FROM test;

参考になったサイトさん：
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