超入門SQL 初心者でも理解できるCOUNT()とSUM() ─ グルーピングと集約関数の基本

以前に掲載して好評を博している「SQL入門」の続編です。前回は基本的なSELECT文を解説しましたが、今回は簡単な集計に挑戦してみます。執筆は前回と同じく、日本MySQLユーザ会副代表の坂井恵さんです。

超入門SQL 初心者でも理解できるCOUNT()とSUM() ─ グルーピングと集約関数の基本

社内に蓄積された大量のデータを、エンジニア以外のスタッフが自分の手で抽出し、集計できる環境を手にする機会も増えてきました。ビジネスの現場にいるメンバーが、自らSQLを使用してデータ操作を行うことができれば、ほしい情報をスピーディーに利用できます。この記事ではそういった「エンジニアではないがSQLを利用できる環境にいる」方に向けて、SQLの便利な集計操作をいろいろと紹介していきます。

本記事では前提条件として、一応はSQLを触ったことがある程度の入門者を対象としています。具体的には、以下の基本的なSELECT文でデータを操作した経験があれば十分です。

  • テーブルを指定して、テーブルの全データを表示する
  • テーブル内の、特定の条件に合う行だけを選択して取得する（WHERE句）
  • テーブル内の、特定の列だけを指定して抽出する
  • 検索結果を並べ替えて取得する（ORDER BY句）

こういったSQLの基本的な操作については、私の過去の記事も参照してください。

▶ SQLをはじめよう - 初心者でもわかる、構文とデータ取得の基本 - エンジニアHub

集計の基本: COUNT(*)関数で件数を取得する

この記事では、次の図に示すitemsテーブルを例に説明します。

2

SQL命令で使うCOUNT(*)をご存じの方も多いでしょう。テーブル内の対象となる行の件数を返すSQLの関数です。itemsテーブルの全件数を表示する例は、次のSQLになります。

SELECT COUNT(*) FROM items;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

ここで注意してほしいのは、COUNT(*)は、必ずしも「テーブルの全部の件数」を返す関数ではなく、「テーブルの対象行の件数」を返すことです。次のようにWHERE句で抽出条件を指定すれば、itemsテーブル内でグループID（group_id）が201である行の件数を得ることができます。

SELECT COUNT(*) FROM items 
 WHERE group_id=201;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

SQLの集計処理入門: GROUP BYによるグルーピング

それではグループIDが201のときだけでなく、全てのグループIDについて行の件数が知りたい場合を考えてみましょう。

ここまで説明した内容で対応するのであれば、先ほどのSQLのWHERE句の値を202や203に変更しながら何度も実行し、その結果をメモしていけばできそうです。ただし、この方法ではグループIDの種類が少ないうちはまだ対応できますが、種類が多くなると同じ操作を何度も繰り返さなければならず、かなり面倒な作業になります。100種類もあったらもうお手上げですね。

ここで威力を発揮するのが、SQLの集計機能です。集計機能はグルーピングとも呼ばれます。GROUP BYという新しい句（SQL文の一部を成す命令）を覚えましょう。今回の例でグループIDごとにグルーピングし、それぞれの件数を知るSQLは次のように書きます。

SELECT COUNT(*)
  FROM items
 GROUP BY group_id;
+----------+
| COUNT(*) |
+----------+
|        5 |
|        3 |
|        2 |
|        1 |
+----------+

グループIDごとに、それぞれの件数を得ることができました。

しかし、このままではどの数字が何の件数なのか分からないので、グループIDも一緒に表示したいところです。SQLの基本を習得している方なら、取得したい列名をSELECT句に記述することを知っているでしょう。次のSQLが、グループIDと件数（COUNT(*)）を取得する例です。

SELECT group_id, COUNT(*)
  FROM items
 GROUP BY group_id;
+----------+----------+
| group_id | COUNT(*) |
+----------+----------+
|      201 |        5 |
|      203 |        3 |
|      202 |        2 |
|      204 |        1 |
+----------+----------+

グループIDごとの件数を1回のSQLで得ることができました。これで、グループIDが100種類あっても怖くありません！

グルーピングを基本的なSQLと組み合わせる

GROUP BYによる集計機能は、基本的なSQL構文であるWHERE句（抽出条件指定）やORDER BY句（並べ替え条件指定）と組み合わせることもできます。

先ほどはitemsテーブルのデータ全件を集計して、結果を出力しました。次に、アイテムID（item_id）が3～10の範囲にある行だけを集計してみましょう。GROUP BY句は、WHERE句の後ろに記述します。

SELECT group_id, COUNT(*)
  FROM items
 WHERE item_id >= 3
   AND item_id <= 10
 GROUP BY group_id;
+----------+----------+
| group_id | COUNT(*) |
+----------+----------+
|      201 |        3 |
|      203 |        2 |
|      202 |        2 |
|      204 |        1 |
+----------+----------+

上記の結果が得られましたが、group_id列の並びがバラバラなのが気になります。グループIDの値が順に並ぶよう、SQLに命令を追加しましょう。ORDER BY句を使用します。

SELECT group_id, COUNT(*)
  FROM items
 WHERE item_id >= 3
   AND item_id <= 10
 GROUP BY group_id
 ORDER BY group_id;
+----------+----------+
| group_id | COUNT(*) |
+----------+----------+
|      201 |        3 |
|      202 |        2 |
|      203 |        2 |
|      204 |        1 |
+----------+----------+

SQLの命令において結果の順序を指定していない場合、取得結果は順不同となることを忘れないでください。ORDER BYを指定しなくても期待する順番になっていることもありますが、それはたまたまです。並び順を指定して結果を取得したい場合は、必ずORDER BY句を使用する習慣を付けましょう。

WHEREとGROUP BYとORDER BYを組み合わせたSQLの読み方

前述の例でSQL文が少し複雑になってきたので、処理の考え方に沿って詳しく解説してみます。

SELECT group_id, COUNT(*)
  FROM items
 WHERE item_id >= 3 AND item_id <= 10
 GROUP BY group_id
 ORDER BY group_id;

このSQLを上から順に見ていきましょう。まず2行目から4行目はそれぞれ次のようになります。

  1. FROM句で「itemsテーブルからの取得」であることを示す（FROM items）
  2. WHERE句で、アイテムIDが3以上かつ10以下（item_id>=3 AND item_id<=10）の行を抽出する
  3. 抽出したデータをグループIDごとに集約する（GROUP BY group_id）

ここまでで、抽出する行の指定が完了しました。次に、抽出する列を次のように考えます。

  1. SELECT句で、group_id列の値と、（GROUP BY句で集計指定した）group_idごとの件数を集計した値を出力するよう指示する（SELECT group_id, COUNT(*)）

これで、取得したいデータセットに関する全ての指定が完了しました。最後に、結果をどのような並び順で出力するかを記述します。

  1. ORDER BY句に、並べ替えたい列のgroup_idを指定する（ORDER BY group_id）

SQL文を書く際に、特に初心者の頃には、前から順に書こうとする方が多いのですが、実際にはここで説明したような順で考えると、書きやすくなります。ぜひ試してみてください。

合計値やさまざまな集約関数で集計してみよう

SQLの集約関数（aggregate function）は、件数を求めるCOUNT()だけではありません。ここでは合計を求めるSUM()など、その他の集約関数を紹介します。

合計を求める集約関数SUM()の使い方

itemsテーブルから、グループIDごとに価格（price）の合計値を集計してみましょう。SQLをどのように書けばよいでしょうか？

先ほど説明したように、前から順に書くのではなく、まず抽出する行について考えます。件数を求める場合と同じように、テーブル内のデータをGROUP BYを使って分割します。取得したい列は「あとで書く」ことにして、ここまでをSQLに書いてみましょう。

SELECT [あとで書く]
  FROM items
 GROUP BY group_id

次に、上記で[あとで書く]としたSELECT句で、取得したい列を検討しましょう。グループIDごとに分割したので、group_idの値は表示したいところです。そして、group_idごとのprice列の合計値を求めたいので、SUM(price)とします。完成したSQLと実行結果は次のようになります。

SELECT group_id, SUM(price)
  FROM items
 GROUP BY group_id;
+----------+------------+
| group_id | SUM(price) |
+----------+------------+
|      201 |       3400 |
|      203 |       2300 |
|      202 |       1500 |
|      204 |        800 |
+----------+------------+

上記の例ではWHERE句やORDER BY句を指定していませんが、実際に使うときには抽出したいデータに応じて指定してください。

その他の集計: 平均、最大、最小

数のデータを扱うときには、行数や合計値だけでなく平均値や、最大・最小の値を取得したい場合もあるでしょう。幸いなことに、SQLには次の集約関数が用意されています。

関数 説明
AVG()1 平均を求める
MAX() 最大値を求める
MIN() 最小値を求める

実行例を見てみましょう。

SELECT group_id, MIN(price), MAX(price), AVG(price)
  FROM items
 GROUP BY group_id;
+----------+------------+------------+------------+
| group_id | MIN(price) | MAX(price) | AVG(price) |
+----------+------------+------------+------------+
|      201 |        300 |       1300 |   680.0000 |
|      203 |        400 |       1000 |   766.6667 |
|      202 |        300 |       1200 |   750.0000 |
|      204 |        800 |        800 |   800.0000 |
+----------+------------+------------+------------+

上記の例では、MIN()、MAX()、AVG()の3つの結果を同時に表示してみました。このようにグルーピング単位が同じの場合には、さまざまな集計関数の結果を同時に取得することができます。

逆に、グルーピング単位が異なるものは、同時に集計できません。例えばgroup_idごとに分けた合計金額と、category_idごとに分けた合計金額を同時に得ることはできません。これは「まずGROUP BYに指定した列によって分割して、その後、それぞれを集計する」という仕組みを知っていれば、容易に理解できるでしょう。

GROUP BY句に書いていない列を取得したいときにどうするか？

これまではグループIDの例をもとに、グルーピングを紹介してきました。ここで少し発展させて、カテゴリごとに集計する例を考えましょう。itemsテーブルにはカテゴリ情報として、カテゴリID（category_id）だけでなく、カテゴリ名（category_name）があります。これらを集計結果とともに取得してみます。

つまり、カテゴリIDでグルーピングして、価格（price）の合計値であるSUM(price)と、あわせてカテゴリIDおよびカテゴリ名を取得したい。前述の例と同じように考えるなら次のSQLになるでしょう。

SELECT category_id, category_name, SUM(price)
  FROM items
 GROUP BY category_id;
→ エラー

しかし、このSQLはエラーになってしまいます。なぜでしょうか？　ひとつずつ丁寧に考えてみましょう。

まず、GROUP BY category_idにより、対象のデータをカテゴリーIDが同じグループに分けます。分割された1つのグループを見てみると、その中にさまざまな値の価格（price）があります。これをどうするのか？　今回は「SUM()関数で合計を求める」を指定します。ここまでなら次のようになるでしょう。

SELECT category_id, SUM(price)
  FROM items
 GROUP BY category_id;

残るカテゴリ名（category_name）列をどうすればいいでしょうか？

データを見てみると、1つのカテゴリIDのグループの中には1種類のカテゴリ名の値だけがあるように見えますが、これは人間がそのように考えて登録したからであって、テーブルの仕組みとしては、1つのカテゴリIDに対して異なるカテゴリ名の値を登録することができます。そのようなデータが存在するデータベースシステムでは、分割したカテゴリIDのグループ内でカテゴリ名を1つに決めることができません。

それでは、どのように書けばよいでしょうか？　2つの考え方があるので、それぞれ紹介します。

(1) category_nameもグルーピング対象にする

category_idだけでなく、「category_idとcategory_nameのセット」でグルーピングするように、GROUP BY句を記述します。つまり、category_nameを分割の条件に含むことで、SELECT句に列名をそのまま書くことができます。

SELECT category_id, category_name, SUM(price)
  FROM items
 GROUP BY category_id, category_name;
+-------------+---------------+------------+
| category_id | category_name | SUM(price) |
+-------------+---------------+------------+
|        1001 | カテゴリ1     |       1800 |
|        1032 | カテゴリ6     |        900 |
|        1002 | カテゴリ2     |        700 |
|        1031 | カテゴリ5     |       1400 |
|        1021 | カテゴリ4     |       1500 |
|        1041 | カテゴリ7     |        800 |
|        1003 | カテゴリ3     |        900 |
+-------------+---------------+------------+

(2) category_nameの値を何らかの方法で1つに決める

GROUP BYの指定はgroup_idのみとしたままで、値を得る部分（SELECT句）を工夫する方法です。この場合の課題は、先ほど説明したように「複数の可能性があるcategory_nameからどれを採用するのかを決める必要がある」ことです。

とはいえこのデータベースでは、多くの場合でcategory_idに対応したcategory_nameが一意に存在するようにデータを登録しています。ですから、どのカテゴリIDに対応するカテゴリ名でもよい（どれでも同じ）ということになりますよね。つまり、どれでも値を1つだけ選んであげればよいでしょう。

このようなときは、MAX()関数を使うことが多いです。どれか1つの行の値に決めるために指定するものなので、MIN()関数でもかまいません（細かい話をすると「対象となる文字列を文字コードで定められた順番に並べて、一番大きいものを採用する」という考え方になります）。

SELECT category_id, MAX(category_name), SUM(price)
  FROM items
 GROUP BY category_id;
+-------------+--------------------+------------+
| category_id | MAX(category_name) | SUM(price) |
+-------------+--------------------+------------+
|        1001 | カテゴリ1          |       1800 |
|        1032 | カテゴリ6          |        900 |
|        1002 | カテゴリ2          |        700 |
|        1031 | カテゴリ5          |       1400 |
|        1021 | カテゴリ4          |       1500 |
|        1041 | カテゴリ7          |        800 |
|        1003 | カテゴリ3          |        900 |
+-------------+--------------------+------------+

どちらの方法がグルーピングの本質に合っているか？

(1)と(2)のどちらで書いても誤りというわけではありませんが、筆者は(2)を好んで使っています。GROUP BYに指定するのは本質的にグルーピングの条件であることを重視しているからです。

今回のケースでは（多くの場合）「カテゴリIDごとにグルーピングしたい」のであって、「カテゴリIDとカテゴリ名のセットでグルーピングしたい」わけではありません。表示上の都合だけでGROUP BY句に指定する列を増やすと、本質が見えにくくなることから、筆者は避けるようにしています。

一方で、(1)の場合は、誤って同じカテゴリIDなのに異なるカテゴリ名を登録してしまったといったことが発生した場合に、グルーピング条件により結果行が分かれて出力されるので間違いに気づきやすいという特徴があります（逆に言えば、分かれて表示されて困るというシーンもあるかもしれません）。

これらの違いを理解した上で、そのときどきで自分に適切な方法を選択できるようになるといいですね。

まとめ ─ 業務を知っている人が必要なデータを自分で加工できるように

大量のデータの中から単純に自分がほしいデータを抽出できるようになるだけでも、データ活用への大きな前進ですが、今回紹介した集計機能を覚えると、さらにデータを有効的に活用できるようになります。業務について知っている人（＝やりたいことを持っている人）が、SQLを使用して自分の力でほしいデータを取得、加工できることは、業務のスピードと質を高めます。ぜひ身に付けておきましょう。

最後に、この記事で学んだGROUP BYを含めたSQL構文の穴埋め図を示しておきます。今回紹介した「考え方」に従って、必要な部分を穴埋めしながら、SQL文を作成する際に活用してください。

3

坂井 恵（さかい・けい） 4 @sakaik

5
日本MySQLユーザ会副代表。データベースを中心とした社内システム設計・コンサルティングを手掛ける有限会社アートライの代表を務める他、SQL関連書籍の執筆も多数。著書に『MySQL徹底入門』（共著、翔泳社刊）など。

編集：はてな編集部

  1. averageの略↩
若手ハイキャリアのスカウト転職