CODEWITHSUNDEEP
mysqlsql
amit
amit

Reputation: 1209

Get record according to country name mysql

Hello everyone i am working with mysql and PHP. I have a table name Doctors and i want to know how many doctors in which country ( count ) , Here is my database 

id      doctor_id       name        address
1       101             abc         Sector 8C, Chandigarh, India, 160008
2       102             xyz         México, Chis., Mexico
3       103             yty         Sector 22, Begum Pur, Delhi, 110086, India
4       104             bhw         1018 Market St, Philadelphia, PA 19107, USA
5       105             byr         Sector 22, Begum Pur, Delhi, 110086, India
6       106             jue         Panama City Beach, FL, USA

I tried with following query but not working properly 

SELECT address,COUNT(*) FROM doctors GROUP BY address

How can i do this ?

Upvotes: 0

Views: 123

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37367

Your data is in very bad format, you can't apply any rule to extract country and group by it. So the closest you can get is to get count for specific country:

SELECT COUNT(*) FROM doctors
WHERE INSTR(address, 'country you want to get count for') > -1

Upvotes: 2

Related Questions