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

Question

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

Go on. Show us as far as you've got. And see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jun 09 '20 at 18:55

vc 74 · Accepted Answer · 2020-06-09T19:07:33.273

1

try this

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

edited Jun 09 '20 at 19:07

answered Jun 09 '20 at 18:55

vc 74

Thank you for your reply but when I run this query, I get the error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as No – learning_always Jun 09 '20 at 18:56
@learning_always And that's what this code does, no? [Fiddle](http://sqlfiddle.com/#!9/699d72/1) – vc 74 Jun 09 '20 at 19:02
Yes, as per fiddle, your code works perfectly But when I am trying to run it using sequel pro, I am getting the error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as No from `table` where `id` between 3 and 9' at line 1 Any guidance please on how to address it? – learning_always Jun 09 '20 at 19:06
You need to use backticks around `table` – vc 74 Jun 09 '20 at 19:07
sorry, I am a novice here. If by backtick you mean 'table' then I am using it – learning_always Jun 09 '20 at 19:09
Yes, like in the above SQL and fiddle – vc 74 Jun 09 '20 at 19:09
I am using it exactly as suggested. count(*) is causing the trouble, but I am unable to figure why – learning_always Jun 09 '20 at 19:12
ok it worked when instead of typing count(*) I just wrote cou and selected count(*) from suggestion Thank you for your time and help. Much appreciated – learning_always Jun 09 '20 at 19:14

1 Answers1