Count for multiple values with single query in mysql

Question

I have a table like

Name      CountryCode 
kabul       AFG
herat       AFG
haag        NLD
tilburg     NLD
breda       NLD
mumbai      IND
delhi       IND
chennai     IND

if want to get the count of city for CountryCode AFG so i will right query select Count(Name) from city where CountryCode = AFG; and i will get the result

Count(Name)
        2

but if I want count for CountryCode AFG,NLD,IND in single query like

count(Name)
        2
        3
        3

How should I write the query to get above result?

i don't know a city called haag in the netherlands. the netherlands does have a city called den haag — Raymond Nijland
– Raymond Nijland, Commented Apr 6, 2018 at 17:40

Pascal · Accepted Answer · 2018-04-06 17:41:36Z

3

SELECT CountryCode, COUNT(*) 
FROM table 
WHERE CountryCode IN ('AFG','NLD','IND') 
GROUP BY CountryCode;

edited Apr 6, 2018 at 17:41

answered Apr 6, 2018 at 17:31

Pascal

2872 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

Should be WHERE CountryCode IN ('AFG','NLD','IND')

Jeremiah Muela · Accepted Answer · 2018-04-06 17:37:46Z

0

So if I understand the question correctly this should work

SELECT COUNT (*) FROM myTable 
  WHERE CountryCode = 'AFG'
  OR CountryCode = 'NLD'
  OR CountryCode = 'IND'

answered Apr 6, 2018 at 17:37

Jeremiah Muela

4441 gold badge6 silver badges16 bronze badges

1 Comment

Pascal Over a year ago

That will get the number of rows with one of those CountryCodes, but not the count of each of the three.

Collectives™ on Stack Overflow

Count for multiple values with single query in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related