Reputation: 7632
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?
Upvotes: 0
Views: 636
Reputation: 1271231
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
.
Upvotes: 1