GROUP BY まとめ(+HAVING)

環境

  • 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)


その他

hrt0kmt.hatenablog.com