mysqlsqlsequelpro

How to find unique count of each value from a table?

How to count the occurrence of each city within a range? I am a beginner to SQL.

I have a table like this:

ID  City 
1   A
2   A
3   A
4   B
5   B
6   C
7   D
8   E
9   C

Using the SQL query:

select `city`
from `table`
where `id` between 3 and 9

I am able to extract the city names which lie between the range of 3 and 9.

How can I get the occurrence of each city within the range:

City No
A    1
B    2
C    2
D    1
E    1
Solution

  • try this

    select city, count(*) as No
from `table`
where id between 3 and 9 
group by city