Java Enthusiast
Java Enthusiast

Reputation: 1191

Counting individual rows in mysql

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

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You can try this:

select name, count(name) from borrower
group by name

Upvotes: 2

Related Questions