Search code examples
mysqlaggregate-functions

GROUP_CONCAT() can't be used by itself?

I have a table of products and want to select all the IDs as a comma separated string. When I run the following query:

SELECT GROUP_CONCAT(p.id SEPARATOR ',') FROM products p GROUP BY 1

I receive the error:

Can't group on 'GROUP_CONCAT(p.id SEPARATOR ',')'

However, if I change the query to:

SELECT id, GROUP_CONCAT(p.id SEPARATOR ',') FROM products p GROUP BY 1

Then it returns a row for each product with two columns, each column with a matching product ID in it. This is not what I want, but I'm showing this query to demonstrate that selecting an additional column causes the error to go away.

Why can't GROUP_CONCAT() can't be used by itself?

Solution

  • If you want to group all found rows into one, then you don't need a GROUP BY clause at all.

    From the docs:

    If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

    You should be able to just do:

    SELECT GROUP_CONCAT(p.id SEPARATOR ',') FROM products p

    Note that SEPARATOR ',' is the default and you can just use:

    SELECT GROUP_CONCAT(p.id) FROM products p

    According to the docs for the SELECT statement, you can pass a position to GROUP BY and it will group by that column.

    Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1 [...] Use of column positions is deprecated because the syntax has been removed from the SQL standard.

    So, GROUP BY 1 is interpreted as GROUP BY GROUP_CONCAT(p.id SEPARATOR ','), which makes no sense.