Subquery and counting

[jamiecottonuk]

HI all,

I have a table called people in my database.

it looks like this

+----+------+
| id | name |
+----+------+
|  2 | bob  |
|  1 | Jill |
|  3 | Jill |
|  4 | John |
|  5 | Jill |
+----+------+

I want to display the distinct name and how many times they are in the table
So like this

Jill - 3
bob - 1
John - 1

How would I go about doing this. I thought maybe something like this but not to sure

SELECT COUNT(name) 
FROM people 
WHERE name = 
(SELECT DISTINCT name 
FROM people);

 

[r937]

select name
     , count(*) as occurrences
  from people
group
    by name

r937.com | rudy.ca