Q: select duplicated record and count record from comma separated in mysql I need a query to select duplicate records and count the total duplicate records,
Posted Records


PostID | Location

  1    | Delhi,Mumbai,Patna
  2    | Mumbai,Noida
  3    | Delhi
  4    | Mumbai,Noida

I would like this result
  Location  | Total
  Delhi     | 2
  Mumbai    | 3
  Patna     | 1
  Noida     | 2


A: First thing is you should normalize your structure get rid of comma separated values and use another table to relate your locations with your posts table  see Database normalization,for you current structure what you can do is get all locations from your table and insert them into new table then use aggregate function on your new table
CREATE TABLE locaions (cities CHAR(255)) ;

SET @S1 = CONCAT(
  "INSERT INTO locaions (cities) VALUES ('",
  REPLACE(
    (SELECT 
      GROUP_CONCAT(`Location`) AS DATA 
    FROM
      `posts`),
    ",",
    "'),('"
  ),
  "');"
) ;

PREPARE stmt1 FROM @s1 ;

EXECUTE stmt1 ;

This will insert all the locations with repeated data in location table and then use below query to get your desired count
SELECT cities,count(*) 
FROM locaions 
group by cities

Demo
