How to suppress duplicates in GROUP_CONCAT MySQL

Question

I use a MySQL select query that gets the names and address of people for address labels. More than one person may live at the same address so I do a GROUP CONCAT to combine their names along with one address.

That correctly gives me salutations like

Mrs Sally G Bloggs & Mr Fred Smith

which is good, but it also give me

Mrs Jean Brown & Mr Harry Brown

which is bad as the surname is the same yet it gets repeated.

Is there a way to do the GROUP_CONCAT line so that I get duplicate surnames only shown once eg

Mrs Sally G Bloggs & Mr Fred Smith
Mrs Jean & Mr Harry Brown

The important bit of the sql I use is

  SELECT
  GROUP_CONCAT(
               CONCAT_WS ( ' ', title , forname_1 , forename_2, surname ) 
               SEPARATOR ' & '
               ) AS Salutation,
  addresses.the_address
  FROM  
       people
  JOIN 
       addresses
  ON  
       addresses.address_id = people.address_id
 GROUP BY  
       people_address_id

(This post mysql GROUP_CONCAT duplicates showed how to remove duplicates from a Group_concat involving just one field but I cannot see how to do it using part of a multi field concat and this one mysql GROUP_CONCAT DISTINCT multiple columns gives a solution but I get a syntax error using it as

GROUP_CONCAT(DISTINCT CONCAT_WS ( ' ', title , forname_1 , forename_2, surname ) GROUP BY surname SEPARATOR ' & ') AS Salutation,

score 2 · Answer 1 · answered Mar 17 '21 at 10:39

You can use 'distinct' in a group_concat() to remove duplicates.

Say for example you have insurance policies with multiple owners. You have a list of names and a list of policies. Alice and Bob are both owners on policy 123, and you don't want:

Names: Alice, Bob, Alice, Bob

You can use:

select group_concat(distinct names.customerNames) from names where names.policyId=policies.policyId) as 'policy owners',

in your join between the 'names' and 'policies' tables.

score 0 · Accepted Answer · answered Sep 30 '14 at 18:53

Your goal is a bit different than that of your linked questions. You only want to remove the duplicate surname, if two persons with the same address have got the same surname.

You can get this with a two step process:

First we concat the titles and both fornames by groups of address and surname
In the outer query we concat the first part and the surnames by groups of addresses only
Addition: we replace the double blanks for persons without a second forename too.

You can use this query:

SELECT 
    REPLACE(GROUP_CONCAT(
        CONCAT_WS(' ', t.name, t.surname) 
        SEPARATOR ' & '
    ), '  ', ' ') AS Salutation,
    addresses.the_address
FROM (
    SELECT
        GROUP_CONCAT(
            CONCAT_WS(' ', title, forname_1, forename_2)
            SEPARATOR ' & '
        ) name,
        surname,
        address_id
    FROM
        people
    GROUP BY
        address_id, surname
) t
INNER JOIN
    addresses
ON
    addresses.address_id = t.address_id
GROUP BY 
    t.address_id;

See it working in this demo This solution takes into account that a third person with a different surname can live at the same address, i.e. a mother-in-law.

Explanation

The inner query

SELECT
    GROUP_CONCAT(
        CONCAT_WS(' ', title, forname_1, forename_2)
        SEPARATOR ' & '
    ) name,
    surname,
    address_id
FROM
    people
GROUP BY
    address_id, surname

concats only the title and the given names of the persons living at the same address and having the same surname (see second query in the demo).

Then we do the same group_concat as you have done before and use in the last step the REPLACE function to eliminate double blanks.

I know we are not supposed to make comments saying 'Thanks' but I'm not too sure how this forum works so thank you for the brilliant answer. Not only does it work exactly as I wanted but you provided the explanation which is even more useful - teach a man to fish.... — user3209752, Sep 30 '14 at 19:31
You're welcome! Glad you could solve your problem with my help and learned how to do such things. "Thank you" comments are not objectionable as far as I know. "Thank you" answers are unwanted of course. — VMai, Sep 30 '14 at 19:38

How to suppress duplicates in GROUP_CONCAT MySQL

2 Answers2

Explanation