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

That would be very easy, if you had a junction table instead of comma separated values. Can you change the database design? Have a look at http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and read the list in the accepted answer, I cite Bill Karwin: **"Hard to count elements in the list, or do other aggregate queries"** — VMai, Jul 05 '14 at 10:15
http://stackoverflow.com/questions/3928325/split-keywords-for-post-php-mysql/3929161 could be of help too. — VMai, Jul 05 '14 at 10:37

score 2 · Answer 1 · answered Jul 05 '14 at 11:10

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

select duplicated record and count record from comma separated in mysql

1 Answers1

Demo

Linked

Related