MySQLクエリで姓ごとのレコード数を新しい列として追加する方法

MySQLでは、COUNT()とGROUP BYを組み合わせたサブクエリを元のテーブルとJOINすることで、各レコードの姓（LastName）が出現する回数を、結果セットに新しい列として表示できます。ここでは、「Miller」という姓が2回登場するデータを例に、具体的な手順を解説します。

1. サンプルテーブルの作成

まず、以下のようにテーブルを作成します。

mysql> create table DemoTable
(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FirstName varchar(100),
    LastName varchar(100)
);
Query OK, 0 rows affected (1.00 sec)

2. レコードの挿入

次に、INSERTコマンドを使ってテーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable(FirstName,LastName) values('David','Miller');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable(FirstName,LastName) values('Carol','Miller');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable(FirstName,LastName) values('John','Doe');
Query OK, 1 row affected (0.21 sec)

3. 挿入したレコードの確認

SELECT文を実行して、テーブル内のすべてのレコードを表示します。

mysql> select *from DemoTable;

このクエリを実行すると、次のような出力が得られます。

+----+-----------+----------+
| Id | FirstName | LastName |
+----+-----------+----------+
|  1 | David     | Miller   |
|  2 | Carol     | Miller   |
|  3 | John      | Doe      |
+----+-----------+----------+
3 rows in set (0.00 sec)

4. 姓ごとの出現回数を追加するクエリ

各姓がテーブル内に何回出現するかを示す「Count」列を追加します。たとえば「Miller」は2回出現しているため、該当する行には「2」が表示されます。これを実現するには、GROUP BYで姓ごとに件数を集計したサブクエリを作成し、元のテーブルとLastNameをキーにJOINします。

mysql> select Id, FirstName, LastName,Count from DemoTable tbl1
   JOIN (select count(*) as Count, LastName from DemoTable GROUP BY LastName) tbl2
   using(LastName);

5. 実行結果

上記のクエリを実行すると、次のような出力が得られます。

+----+-----------+----------+-------+
| Id | FirstName | LastName | Count |
+----+-----------+----------+-------+
|  1 | David     | Miller   |     2 |
|  2 | Carol     | Miller   |     2 |
|  3 | John      | Doe      |     1 |
+----+-----------+----------+-------+
3 rows in set (0.00 sec)

仕組みの解説

上の結果を見ると、DavidとCarolの行には「Miller」の出現回数である「2」が、Johnの行には「Doe」の出現回数である「1」がそれぞれ表示されていることがわかります。

ポイントは次の2点です。

• サブクエリ内でcount(*)をCountという別名にし、GROUP BY LastNameによって姓ごとのレコード件数を事前に集計しています。
• using(LastName)により、元のテーブル（tbl1）と集計結果（tbl2）を姓をキーに結合することで、元の各行に対応する出現回数を新しい列として付加しています。

この手法を使えば、氏名だけでなく、カテゴリや部署名など、任意の列の値ごとの出現回数を簡単に結果セットへ含めることができます。