Get Count Of Different Values In Comma Separated Row In Mysql

March 31, 2024 Post a Comment

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

Solution 1:

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

createtable test
(jobid int ,city varchar(100));

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

Now to get the count you can use the following

selectcount(*) as tot from test
wherefind_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

createtable jobs (jobid int, name varchar(100));

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

createtable locations (id int, name varchar(100));
insertinto locations values (1,'New York'),(2,'Ohio'),(3,'Virginia');

createtable job_locations (id int, jobid int, location_id int);

insertinto 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

selectcount(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
groupby l.name

DEMO

Solution 2:

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 ','), ',', '')) +1AS count_city
  FROM table_name
) t
JOIN numbers n
ON n.num <= t.count_city
GROUPBY one_city
ORDERBY 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; "

Solution 3:

SELECTCOUNT(*) 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 ...

Solution 4:

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
-------------------------------------------------1New York
2New York
2                   Ohio
2                   Virginia
3New York
3                   Virginia

Now you make the query will be something like this

select city, count(jobid) FROMTABLEGROUPBY city  //Result will be New York 3, Ohio 1and Virginia 2

Solution 5:

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

Job ID      City Name
1New York
2New 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?

Lab Sql