Get Count of different values in comma separated row in mysql

Question

A table Jobs which have 2 column JobId, City when we save job a job location may be multiple city like below

-----------------------------
JobId               City
-------------------------------
1                   New York
2                   New York , Ohio , Virginia
3                   New York , Virginia

how i count jobid in perticular city like i want count of jobid in New York city i want result like

New York 3 Ohio 1 Virginia 2

i tied it by group by cityname it give city name 3 time in case of new york and count 3 — Ashutosh SIngh, Nov 27 '14 at 07:30
Select city,Count(*) from Table group by city; but it's not useful for me @EternalHour — Ashutosh SIngh, Nov 27 '14 at 07:38

Abhik Chakraborty · Answer 1 · 2014-11-27T07:59:21.690

Your database is poorly designed and you are going to have a lot of trouble down the line. Using the current structure you can get the count using the find_in_set function but that you should avoid .

Your table is as

create table test
(jobid int ,city varchar(100));

insert into test values 
(1,'New York'),
(2,'New York, Ohio,Virginia'),
(3,'New York,Virginia');

Now to get the count you can use the following

select 
count(*) as tot from test
where 
find_in_set('Virginia',city) > 0;

As I mentioned this is a poor db design the ideal would be as

first a job table with job details
a location table containing all the locations
and finally a table linking a job and a location

So it would look like

create table jobs (jobid int, name varchar(100));

insert into jobs values
(1,'PHP'),(2,'Mysql'),(3,'Oracle');

create table locations (id int, name varchar(100));
insert into locations values (1,'New York'),(2,'Ohio'),(3,'Virginia');

create table job_locations (id int, jobid int, location_id int);

insert into job_locations values
(1,1,1),(2,2,1),(3,2,2),(4,2,3),(5,3,1),(6,3,3);

Now getting the count and many more operations will be fairly easy

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

For counting all the jobs per city and using the above schema it would very simple

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

DEMO

I want count of id for all city at a time like New York 3 Virginia 2 Ohio 1 — Ashutosh SIngh, Nov 27 '14 at 07:55

score 2 · Accepted Answer · answered Dec 02 '14 at 11:23

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(all_city, ',', num), ',', -1) AS one_city,
  COUNT(*) AS cnt
FROM (
  SELECT
    GROUP_CONCAT(city separator ',') AS all_city,
    LENGTH(GROUP_CONCAT(citySEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(citySEPARATOR ','), ',', '')) + 1 AS count_city
  FROM table_name
) t
JOIN numbers n
ON n.num <= t.count_city
GROUP BY one_city
ORDER BY cnt DESC;

for getting count of comma separated distinct value run above query but getting correct resulr you should use one more table **numbers** which have only one column num integer type and insert some values. if you getting error during GROUP_CONCAT(city separator ',') AS all_city in this condition set a global variable " SET group_concat_max_len = 18446744073709547520; "

This doesn't work: https://www.db-fiddle.com/f/nSjT5XVcskixjxiSbGczTg/0 — Muhammad Hassan, Apr 23 '20 at 17:00

score 1 · Answer 3 · answered Nov 27 '14 at 07:53

SELECT COUNT(*) AS jobs 
  FROM Jobs
 WHERE FIELD_IN_SET('New York') > 0
;

You should read about database normalization though. Having a comma separated list of values in a database table always has a 'smell', e.g. you can only check for a specific city name here and can't easily create a list of job counts for all cities referred to in the job table in one go ...

See e.g. http://en.wikipedia.org/wiki/Database_normalization for a start ...

Junaid · Answer 4 · 2014-11-27T07:54:16.267

0

Make JobId and City column as joined primary key. This will make your life easier. Do not insert multiple cities separated by commas.

-------------------------------------------------
JobId               City         // Other columns
-------------------------------------------------
1                   New York
2                   New York
2                   Ohio
2                   Virginia
3                   New York
3                   Virginia

Now you make the query will be something like this

select city, count(jobid) FROM TABLE GROUP BY city  // Result will be New York 3, Ohio 1 and Virginia 2

edited Nov 27 '14 at 07:54

answered Nov 27 '14 at 07:38

Junaid

2,572
6
41
77

according to your suggestion i am able to count only one city count but i want all city job count at a time – Ashutosh SIngh Nov 27 '14 at 07:42
1

SELECT COUNT(jobid), city FROM TABLE GROUP BY city – M.Svrcek Nov 27 '14 at 07:46
I want count of id corresponding to all city – Ashutosh SIngh Nov 27 '14 at 07:46
You want the result 4 (it counts repeating cites just one time) **OR** result 6 (it does NOT count repeating cities just one time) ... So what result you want ?? – Junaid Nov 27 '14 at 07:51
i want result like new york 3 virginia 2 Ohio 1 – Ashutosh SIngh Nov 27 '14 at 07:52
@Junaid you are twice selecting city – M.Svrcek Nov 27 '14 at 07:55

score 0 · Answer 5 · edited May 23 '17 at 12:25

0

Design your table as mentioned below. Each city name and Job Id in each row.

Job ID      City Name
1           New York
2           New York
1           Seattle

Then use the query as mentioned in the below link.

SQL: How to get the count of each distinct value in a column?

edited May 23 '17 at 12:25

Community

1
1

answered Nov 27 '14 at 07:56

Kannan Rajendran

220
1
16

score 0 · Answer 6 · answered Nov 27 '14 at 09:10

0

Try this query,

 SELECT COUNT(jobid), city FROM TABLE where city like '%newyork%';

answered Nov 27 '14 at 09:10

Get Count of different values in comma separated row in mysql

6 Answers6

Linked