sql GROUP within group

Question

Let's say I have this database:

ID  | Name   | City  
1   | John   | TLV
2   | Abe    | JLM
3   | John   | JLM

I want to know how many people with different names are in each city. I tried to use GROUP BY like this:

SELECT `city`, count(`index`) as `num` FROM `people`
GROUP BY `city`, `name`

But this seems to group by both.

City  | num
TLV   | 1
JLM   | 1

What I want to do is to group by city, and group the results by name.

City  | num
TLV   | 1
JLM   | 2

How can I do this?

Answer 1

I think you want this:

SELECT `city`, count(distinct name) as `num`
FROM `people`
GROUP BY `city`;

You might want just count(name) . . . I'm not sure what you mean by "differently named". count(name) is preferable, if you don't need the distinct.