Reputation: 1191
I have the following loan schema:
mysql> select * from borrower;
+----------+---------+
| name | loan_id |
+----------+---------+
| Adams | L16 |
| Curry | L93 |
| Hayes | L15 |
| Jackson | L14 |
| Jones | L17 |
| Smith | L11 |
| Smith | L23 |
| Williams | L17 |
+----------+---------+
8 rows in set (0.00 sec)
I need to calculate the total number of loans, every person has taken. I thought self-join is the solution to this and I tried the following query:
select T1.name, count(T2.name) from borrower T1, borrower T2 where T1.name = T2.name;
But I get weird results as following:
mysql> select T1.name, count(T2.name) from borrower T1, borrower T2 where T1.name = T2.name;
+-------+----------------+
| name | count(T2.name) |
+-------+----------------+
| Adams | 10 |
+-------+----------------+
1 row in set (0.00 sec)
Upvotes: 0
Views: 26
Reputation: 172628
You can try this:
select name, count(name) from borrower
group by name
Upvotes: 2