select duplicated record and count record from comma separated in mysql

Question

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

Answer 1

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