CODEWITHSUNDEEP
phpmysqlsql
Ricardo
Ricardo

Reputation: 11

find duplicates and merge their values in sql

I have a database table that contains a list of schools and names:

school    name    point
------------------------
first     dani    good
first     dani    precise
third     John    nice
first     dani    pro
third     John    cute

I want to end up with just one record for each school / name combination and merge the point values like this:

school    name    point
--------------------------------------
first     dani    good and precise and pro
third     John    cute and nice

Upvotes: 1

Views: 80

Answers (3)

davejal
davejal

Reputation: 6143

Using @ebahi's answer:

Change the query into as mentioned by @fuzzytree:

SELECT school, name, GROUP_CONCAT(distinct point SEPARATOR ' and ') points
FROM table
GROUP BY school, name

sqlfiddle to see the result

Upvotes: 1

user6307642
user6307642

Reputation:

You can use GROUP_CONCAT like that to get the results :

SELECT school, name, GROUP_CONCAT(point SEPARATOR ' and ') points
FROM table
GROUP BY school, name

If it's ok and you want to replace your actually table with the results, create the same table with another name tmptable for example.

INSERT INTO tmptable
SELECT school, name, GROUP_CONCAT(point SEPARATOR ' and ') points
FROM table
GROUP BY school, name;

DROP TABLE table;
RENAME TABLE tmptable TO table;

Here you see the result in an sqlfiddle

Upvotes: 3

Bernd Buffen
Bernd Buffen

Reputation: 15057

here is a sample that will works for you

and a new one with DISTINCT

SELECT
  school,
  `name`,
  GROUP_CONCAT( `point` SEPARATOR ' and ')
FROM ( SELECT DISTINCT school,`name`, `point` FROM groupme) AS result
GROUP BY school,`NAME`;

SELECT
  school,
  `name`,
  GROUP_CONCAT( `point` SEPARATOR ' and ')
FROM groupme
GROUP BY school,`name`;

Sample

MariaDB [mysql]> select * from groupme;
+----+--------+------+---------+
| id | school | name | point   |
+----+--------+------+---------+
|  1 | first  | dani | good    |
|  2 | first  | dani | precise |
|  3 | third  | John | nice    |
|  4 | first  | dani | pro     |
|  5 | third  | John | cute    |
+----+--------+------+---------+
5 rows in set (0.01 sec)

MariaDB [mysql]> SELECT school, `name` , GROUP_CONCAT( `point` SEPARATOR ' and ')
    -> FROM groupme
    -> GROUP BY school,`name`;
+--------+------+------------------------------------------+
| school | name | GROUP_CONCAT( `point` SEPARATOR ' and ') |
+--------+------+------------------------------------------+
| first  | dani | good and precise and pro                 |
| third  | John | nice and cute                            |
+--------+------+------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]>

Upvotes: 1

Related Questions