1対多のテーブルで集計結果を表示する方法は?
1対多の関係にある2つのテーブルを結合して、その集計結果を表示する方法はどうすればいいのでしょうか?
clientsとinvestmentsのテーブルがあり、それぞれに以下のデータを挿入しているとします。
create table clients (
client_id int not null auto_increment primary key,
email varchar(255)
);
create table investments (
investment_id int not null auto_increment primary key,
amount numeric(8,2),
client_id int,
foreign key (client_id) references clients(client_id)
);
mysql> select * from clients;
+-----------+-------------+
| client_id | email |
+-----------+-------------+
| 1 | 111@aaa.com |
| 2 | 222@aaa.com |
| 3 | 333@aaa.com |
| 4 | 444@aaa.com |
| 5 | 555@aaa.com |
+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select * from investments;
+---------------+--------+-----------+
| investment_id | amount | client_id |
+---------------+--------+-----------+
| 1 | 101.00 | 1 |
| 2 | 102.00 | 1 |
| 3 | 103.00 | 1 |
| 4 | 201.00 | 2 |
| 5 | 202.00 | 2 |
| 6 | 301.00 | 3 |
+---------------+--------+-----------+
これらのテーブルを結合して、client毎にメールアドレスとinvestmentsの数、investmentsの合計金額を出したいと考えています。
mysql> select client_id, count(*), sum(amount) from investments group by client_id;
+-----------+----------+-------------+
| client_id | count(*) | sum(amount) |
+-----------+----------+-------------+
| 1 | 3 | 306.00 |
| 2 | 2 | 403.00 |
| 3 | 1 | 301.00 |
+-----------+----------+-------------+
この結果にメールアドレスを足すイメージです。
+-----------+----------+-------------+-------------+
| client_id | email | count(*) | sum(amount) |
+-----------+----------+-------------+-------------+
どうやってもうまくemailが入りません。どうすればいいのでしょうか?