概要
グループ化はGROUP BY文でグループ化する列を指定する。ほとんどの場合、以下のように集約関数を適用する。
SELECT FUNC(col) FROM table GROUP BY col;
上記の分では、table中の列colによってグループ化し、そのグループごとに集約関数FUNC()を適用した結果を返す。
実行例
以下のデータを使う。学生ごとの履修科目と得点のデータのイメージ。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT * FROM student_scores; +------+---------+-------+ | name | subject | score | +------+---------+-------+ | 安藤 | 数学 | 80 | | 安藤 | 物理 | 90 | | 安藤 | 英語 | 75 | | 伊藤 | 数学 | 65 | | 伊藤 | 文学 | 85 | | 宇藤 | 物理 | 70 | | 宇藤 | 英語 | 85 | | 宇藤 | 文学 | 95 | | 江藤 | 数学 | 85 | | 江藤 | 物理 | 80 | | 江藤 | 文学 | 65 | +------+---------+-------+ 11 rows in set (0.00 sec) |
COUNTの例
以下の例では、学生でグルーピングして科目数をカウントしている。これにより、各学生の履修科目数が得られる。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT name, COUNT(subject) FROM student_scores -> GROUP BY name; +------+----------------+ | name | COUNT(subject) | +------+----------------+ | 安藤 | 3 | | 伊藤 | 2 | | 宇藤 | 3 | | 江藤 | 3 | +------+----------------+ 4 rows in set (0.00 sec) |
以下の例では、科目でグルーピングして科目数をカウントしている。これにより、各科目の被履修数が得られる。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT subject, COUNT(subject) FROM student_scores -> GROUP BY subject; +---------+----------------+ | subject | COUNT(subject) | +---------+----------------+ | 数学 | 3 | | 物理 | 3 | | 英語 | 2 | | 文学 | 3 | +---------+----------------+ 4 rows in set (0.00 sec) |
MIN/MAXの例
以下の例では、科目でグルーピングして点数の最小値と最大値を表示させている。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores -> GROUP BY subject; +---------+------------+------------+ | subject | MIN(score) | MAX(score) | +---------+------------+------------+ | 数学 | 65 | 85 | | 物理 | 70 | 90 | | 英語 | 75 | 85 | | 文学 | 65 | 95 | +---------+------------+------------+ 4 rows in set (0.00 sec) |
SELECTでsubjectも表示させている。上記の例ではsubjectでグルーピングしているので適切に表示されるが、subjectの値に使われるレコードは不定なので不適切に使うと以下のようになる。
|
1 2 3 4 5 6 7 |
mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores; +---------+------------+------------+ | subject | MIN(score) | MAX(score) | +---------+------------+------------+ | 数学 | 65 | 95 | +---------+------------+------------+ 1 row in set (0.00 sec) |
SUM/AVGの例
以下の例では、学生ごとの合計点数と教科ごとの平均点数をグルーピングにより計算している。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT name, SUM(score) FROM student_scores -> GROUP BY name; +------+------------+ | name | SUM(score) | +------+------------+ | 安藤 | 245 | | 伊藤 | 150 | | 宇藤 | 250 | | 江藤 | 230 | +------+------------+ 4 rows in set (0.00 sec) mysql> SELECT subject, AVG(score) FROM student_scores -> GROUP BY subject; +---------+------------+ | subject | AVG(score) | +---------+------------+ | 数学 | 76.6667 | | 物理 | 80.0000 | | 英語 | 80.0000 | | 文学 | 81.6667 | +---------+------------+ 4 rows in set (0.00 sec) |
HAVING
各科目が履修された数は以下のようだった。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT subject, COUNT(subject) FROM student_scores -> GROUP BY subject; +---------+----------------+ | subject | COUNT(subject) | +---------+----------------+ | 数学 | 3 | | 物理 | 3 | | 英語 | 2 | | 文学 | 3 | +---------+----------------+ 4 rows in set (0.00 sec) |
ここで、履修数が2よりも多い科目だけを抽出したいとする。
WHERE句でできそうだがエラーになる。
|
1 2 3 4 5 |
mysql> SELECT subject, count(subject) FROM student_scores -> GROUP BY subject -> WHERE count(subject) > 2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count(subject) > 2' at line 3 |
集約結果によって抽出したい場合は、HAVING句を使う。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT subject, count(subject) FROM student_scores -> GROUP BY subject -> HAVING count(subject) > 2; +---------+----------------+ | subject | count(subject) | +---------+----------------+ | 数学 | 3 | | 物理 | 3 | | 文学 | 3 | +---------+----------------+ 3 rows in set (0.00 sec) |
WHERE句との組み合わせの例。scoreが80以上の履修数を科目ごとにカウント。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT subject, count(subject) FROM student_scores -> WHERE score >= 80 -> GROUP BY subject; +---------+----------------+ | subject | count(subject) | +---------+----------------+ | 数学 | 2 | | 物理 | 2 | | 文学 | 2 | | 英語 | 1 | +---------+----------------+ 4 rows in set (0.00 sec) |
さらに、scoreが80以上の数が1より大きい科目のみHAVING句で取り出す。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT subject, count(subject) FROM student_scores -> WHERE score >= 80 -> GROUP BY subject -> HAVING count(subject) > 1; +---------+----------------+ | subject | count(subject) | +---------+----------------+ | 数学 | 2 | | 物理 | 2 | | 文学 | 2 | +---------+----------------+ 3 rows in set (0.00 sec) |