sqlsqlitegroup-bydelimitergroup-concat

How can I use DISTINCT in group_concat() while also using its delimiter parameter?

I want the name for every town and group by town:

SELECT Town, GROUP_CONCAT(DISTINCT name,';')
FROM MY_TABLE
WHERE action IS NOT NULL
GROUP BY Town;

';' in GROUP_CONCAT specifies the delimiter. If I use DISTINCT and the delimiter it returns:

Error: DISTINCT aggregates must have exactly one argument

DISTINCT without the delimiter parameter works, delimiter without DISTINCT works too. How can I use DISTINCT in group_concat() while also using its delimiter parameter?

Solution

  • You could try first using a subquery to yield all distinct names for each town. Then, do your current aggregation:

    SELECT
    Town, GROUP_CONCAT(name, ';')
FROM
(
    SELECT Town, name
    FROM MY_TABLE
    WHERE action IS NOT NULL
    GROUP BY Town, name
) t
GROUP BY Town;