Mysql – SELECT query to find rows for specific entry

MySQL

I have a table location. And I want to extract rows from it for the column pattern.

And I did this query SELECT COUNT(DISTINCT username) FROM location;
+--------------------------+
| COUNT(DISTINCT username) |
+--------------------------+
| 58636 |
+--------------------------+

So I got the whole number of rows.

My question is: how to get rows for each different username entry but not on the whole. The username meaning is not known, and goal to see how many rows each username has.

So for e.g.

username Nick – 10 rows

username Paul – 20 rows

Best Answer

I think you are looking for a simple GROUP BY

SELECT username, COUNT(*) 
 FROM location
group by username;