ゼロから学ぶ MySQL 基礎の基礎(5) MySQLでいろいろな計算をしてみよう - 合計、平均、最大・最小値

今回は、テーブルに入力したデータで合計や平均、最大・最小値の導出など簡単な計算を行います。その前に、前回削除した高橋三郎君を復活させたうえで、メンバー全員の理科の得点を入力しておきます。

mysql> select * from first;
+-------------------------------------------------------------------------------------+
| id | name　　  | jpn | math | eng | sci | created             | modified　　　　　  |
+-------------------------------------------------------------------------------------+
|  1 | 佐藤一郎　|  94 |   87 |  60 |  39 | 2015-05-14 04:49:07 | 2015-05-14 04:49:07 |
|  2 | 鈴木二郎　|  62 |   80 |  49 |  96 | 2015-05-14 04:56:38 | 2015-05-14 04:56:38 |
|  3 | 高橋三郎　|  80 |   80 |  80 |  78 | 2015-05-14 04:56:38 | 2015-05-14 04:56:38 |
|  4 | 田中四郎　|  78 |   59 |  42 |  72 | 2015-05-14 04:56:38 | 2015-05-14 04:56:38 |
|  5 | 伊藤五郎　|  42 |   87 |  56 |  61 | 2015-05-14 04:56:38 | 2015-05-14 04:56:38 |
+-------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

和(SUM)

まずは、4科目の合計点を導出してみます。この場合、端末上で簡単に計算することが可能です。

mysql> select id, name, jpn + math + eng + sci from first;
+-----------------------------------------+
| id | name　　  | jpn + math + eng + sci |
+------------------------------------------+
|  1 | 佐藤一郎　|                    280 |
|  2 | 鈴木二郎　|                    287 |
|  3 | 高橋三郎　|                    318 |
|  4 | 田中四郎　|                    251 |
|  5 | 伊藤五郎　|                    246 |
+-----------------------------------------+
5 rows in set (0.00 sec)

もちろん、合計点を昇順や降順に並べ替えることもできます。また、現実的にはあまりない状況かもしれませんが、国語のみの合計点を求める場合には、SUM関数が利用できます。

mysql> select sum(jpn) from first;
+----------+
| sum(jpn) |
+----------+
|      356 |
+----------+
1 row in set (0.00 sec)

平均(AVERAGE)

平均点の導出にはAVERAGE関数が利用できます。

mysql> select avg(jpn) from first;
+----------+
| avg(jpn) |
+----------+
|  71.2000 |
+----------+
1 row in set (0.00 sec)

カウント(COUNT)

また、レコードの件数を求めるにはCOUNT関数を使います。今回は簡単に数えることができますが、レコードが数百件を超えると、数えることは至難の技です。

mysql> select count(id) from first;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

もちろん、ここに条件を加えることもできます。たとえば、理科で75点以上獲得した学生の数を知りたい場合、以下の命令を実行します。

mysql> select count(sci) from first where sci >= 75;
+------------+
| count(sci) |
+------------+
|         2  |
+------------+
1 row in set (0.00 sec)

最大、最小(MAX、MIN)

最後に、最大値・最小値の求め方についてです。SUM関数、AVERAGE関数などと同様にMAX関数とMIN関数を使うことで、各フィールドの最大・最小を求めることができます。

mysql> select max(jpn), min(math) from first;
+----------+-----------+
| max(jpn) | min(math) |
+----------+-----------+
|       94 |        59 |
+----------+-----------+
1 row in set (0.00 sec)

国語の最高点が94点、数学の最低点が59点ということはわかりました。しかし、これでは誰が最高点、最低点であるのかがわかりません。氏名を一緒に表示させるにはどうすればよいでしょうか? 簡単のため、数学の最低点の場合に限定して話を進めていきたいと思います。

まず、以下の命令を実行してみましょう。

select name, min(math) from first;

一見それらしい命令ではありますが、これでは正しい結果(name：田中四郎、min(math)：59点)を出せていません。「where句」を利用して「数学最低点の人物の氏名を引き出すこと」が必要です。以下では2通りの方法を用いて、正しい結果を引き出していきます。

入れ子構造を利用

「数学最低点の人物の氏名を引き出す」ためには、どうすれば良いのでしょうか。単純に考えると、以下の命令が実現できれば、数学最低点の氏名(name)と得点(math)が浮かび上がることがわかります。

mysql> select name, math from first where math = 数学最低点;

つまり、「数学最低点」のところに数学最低点の情報を抽出する方法がわかれば問題解決です。少し難しく思えますが、これまでに何回も利用してきた「SELECT文」を使えば、どのような情報でも抽出することができるはずです。

mysql> select name, math from first where math = (select min(math) from first);
+-----------+-----------+
| name　 　 | min(math) |
+-----------+-----------+
| 田中四郎　|        59 |
+-----------+-----------+
1 row in set (0.00 sec)

このように、SELECT文の中にSELECT文が登場する「入れ子構造」を作ることにより、正しい情報を抽出することができました。ほかのプログラミング言語と同様に、命令の方法がわからない場合には、今回のように日本語で表現してみると解決の糸口が見えることが多いので、ぜひ試してみてください。

limitの利用

それでは、もうひとつの方法で情報を抽出してみたいと思います。実は、where句を利用しなくても今回の問題は解決できます。答えは、「昇順に並べて一番上の行だけ抽出する」方法です。

まずは、昇順に並べるために以下の命令を実行します。

mysql> select name, math from first order by math；
+-------------+-------+
| name　　  　| math  |
+-------------+-------+
| 田中四郎　　|    59 |
| 鈴木二郎　　|    80 |
| 高橋三郎　　|    80 |
| 佐藤一郎　　|    87 |
| 伊藤五郎　　|    87 |
+-------------+-------+
5 rows in set (0.00 sec)

この結果から一番上の行だけ抽出できれば、問題解決です。それを実現する命令が「limit」です。

mysql> select name, math from first order by math limit 1；
+------------+------+
| name　　　 | math |
+------------+------+
| 田中四郎 　|   59 |
+------------+------+
1 row in set (0.00 sec)

limitの後の数字は、1番上の行のみを抽出するという意味です。limit 2では1、2番目の行が、limit 3では1～3番目の行が抽出されることになります。

執筆者紹介