database - Get Count of different values in comma separated row in mysql -

a table jobs have 2 column jobid, city when save job job location may multiple city below

----------------------------- jobid               city ------------------------------- 1                   new york 2                   new york , ohio , virginia 3                   new york , virginia 

how count jobid in perticular city want count of jobid in new york city want result

new york 3 ohio 1 virginia 2

your database poorly designed , going have lot of trouble down line. using current structure can count using find_in_set function should avoid .

your table as

create table test (jobid int ,city varchar(100));  insert test values  (1,'new york'), (2,'new york, ohio,virginia'), (3,'new york,virginia'); 

now count can use following

select  count(*) tot test  find_in_set('virginia',city) > 0; 

as mentioned poor db design ideal as

• first job table job details
• a location table containing locations
• and table linking job , location

so like

create table jobs (jobid int, name varchar(100));  insert jobs values (1,'php'),(2,'mysql'),(3,'oracle');  create table locations (id int, name varchar(100)); insert locations values (1,'new york'),(2,'ohio'),(3,'virginia');  create table job_locations (id int, jobid int, location_id int);  insert job_locations values (1,1,1),(2,2,1),(3,2,2),(4,2,3),(5,3,1),(6,3,3); 

now getting count , many more operations easy

select count(j.jobid)  tot jobs j  join job_locations jl on jl.jobid = j.jobid join locations l on l.id = jl.location_id l.name = 'virginia' 

for counting jobs per city , using above schema simple

select l.name, count(j.jobid)  tot jobs j  join job_locations jl on jl.jobid = j.jobid join locations l on l.id = jl.location_id group l.name 

demo