環境
- Server version: 5.6.27 Source distribution
文法
- グループごとに分けてから抽出
- SELECT 計算/集計したカラム FROM テーブル GROUP BY グループ化するカラム HAVING 条件
- 抽出してからグループに分ける
- SELECT 計算/集計したカラム FROM テーブル WHERE 抽出条件 GROUP BY グループ化するカラム
初期データ
mysql> desc test_table2;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| No. | int(11) | YES | | NULL | |
| ban | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> select * from test_table2;
+----+------+------+--------+
| id | No. | ban | name |
+----+------+------+--------+
| 1 | 9 | 83 | name1 |
| 2 | 10 | 49 | name2 |
| 3 | 2 | 95 | name3 |
| 4 | 2 | 30 | name4 |
| 6 | 4 | 62 | name6 |
| 7 | 2 | 18 | name7 |
| 8 | 8 | 6 | name8 |
| 9 | 5 | 75 | name9 |
| 13 | 8 | 56 | name13 |
| 14 | 6 | 56 | name14 |
| 15 | 5 | 10 | name15 |
| 16 | 8 | 83 | name16 |
| 17 | 6 | 85 | name17 |
| 18 | 2 | 74 | name18 |
| 19 | 3 | 13 | name19 |
| 20 | 8 | 44 | name20 |
| 28 | 9 | 78 | name28 |
| 29 | 1 | 61 | name29 |
| 30 | 8 | 68 | name30 |
| 31 | 8 | 55 | name31 |
| 32 | 6 | 73 | name32 |
| 33 | 2 | 100 | name33 |
| 34 | 5 | 82 | name34 |
| 35 | 7 | 7 | name35 |
| 36 | 10 | 90 | name36 |
| 37 | 8 | 29 | name37 |
| 38 | 8 | 75 | name38 |
| 39 | 6 | 87 | name39 |
| 40 | 8 | 11 | name40 |
| 41 | 10 | 93 | name41 |
| 42 | 4 | 30 | name42 |
| 43 | 1 | 70 | name43 |
+----+------+------+--------+
32 rows in set (0.00 sec)
単一カラム指定
// 先勝ちとなる
mysql> select * from test_table2 group by `No.`;
+----+------+------+--------+
| id | No. | ban | name |
+----+------+------+--------+
| 29 | 1 | 61 | name29 |
| 3 | 2 | 95 | name3 |
| 19 | 3 | 13 | name19 |
| 6 | 4 | 62 | name6 |
| 9 | 5 | 75 | name9 |
| 14 | 6 | 56 | name14 |
| 35 | 7 | 7 | name35 |
| 8 | 8 | 6 | name8 |
| 1 | 9 | 83 | name1 |
| 2 | 10 | 49 | name2 |
+----+------+------+--------+
10 rows in set (0.00 sec)
件数表示
// No.が同一のデータの件数を割り出す
mysql> select `No.`, count(*) as COUNT from test_table2 group by `No.`;
+------+--------+
| No. | COUNT |
+------+--------+
| 1 | 2 |
| 2 | 5 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 1 |
| 8 | 9 |
| 9 | 2 |
| 10 | 3 |
+------+--------+
10 rows in set (0.00 sec)
合計値割り出し
mysql> select `No.`, sum(ban) as `SUM` from test_table2 group by `No.`;
+------+------+
| No. | SUM |
+------+------+
| 1 | 131 |
| 2 | 317 |
| 3 | 13 |
| 4 | 92 |
| 5 | 167 |
| 6 | 301 |
| 7 | 7 |
| 8 | 427 |
| 9 | 161 |
| 10 | 232 |
+------+------+
10 rows in set (0.00 sec)
平均値割り出し
mysql> select `No.`, avg(ban) as `AVERAGE` from test_table2 group by `No.`;
+------+---------+
| No. | AVERAGE |
+------+---------+
| 1 | 65.5000 |
| 2 | 63.4000 |
| 3 | 13.0000 |
| 4 | 46.0000 |
| 5 | 55.6667 |
| 6 | 75.2500 |
| 7 | 7.0000 |
| 8 | 47.4444 |
| 9 | 80.5000 |
| 10 | 77.3333 |
+------+---------+
10 rows in set (0.00 sec)
HAVING
グループ化した後、条件をつけて表示
// 3以上の合計値を持つbanを表示する
mysql> select `No.`, count(ban) as `banの件数が3以上` from `test_table2` group by `No.` having count(ban) >= 3;
+------+------------------------+
| No. | banの件数が3以上 |
+------+------------------------+
| 2 | 5 |
| 5 | 3 |
| 6 | 4 |
| 8 | 9 |
| 10 | 3 |
+------+------------------------+
5 rows in set (0.00 sec)
抽出後、グループ化を行う
mysql> select `No.`, sum(ban) from test_table2 where ban >=5 group by `No.` order by `No.`;
+------+----------+
| No. | sum(ban) |
+------+----------+
| 1 | 131 |
| 2 | 317 |
| 3 | 13 |
| 4 | 92 |
| 5 | 167 |
| 6 | 301 |
| 7 | 7 |
| 8 | 427 |
| 9 | 161 |
| 10 | 232 |
+------+----------+
10 rows in set (0.00 sec)
mysql> select `No.`,`ban` from test_table2 order by `No.`;
+------+------+
| No. | ban |
+------+------+
| 1 | 70 |
| 1 | 61 |
| 2 | 100 |
| 2 | 74 |
| 2 | 18 |
| 2 | 95 |
| 2 | 30 |
| 3 | 13 |
| 4 | 62 |
| 4 | 30 |
| 5 | 75 |
| 5 | 82 |
| 5 | 10 |
| 6 | 85 |
| 6 | 56 |
| 6 | 87 |
| 6 | 73 |
| 7 | 7 |
| 8 | 29 |
| 8 | 55 |
| 8 | 68 |
| 8 | 11 |
| 8 | 44 |
| 8 | 83 |
| 8 | 56 |
| 8 | 6 |
| 8 | 75 |
| 9 | 78 |
| 9 | 83 |
| 10 | 90 |
| 10 | 93 |
| 10 | 49 |
+------+------+
32 rows in set (0.00 sec)
