GROUP BY with non-aggregated columns

With GROUP BY customer_id, a query returns one row per customer. If you also select customer_name and a customer has two different names across their rows, the database has no rule for which name to show. Most engines refuse the query rather than pick one quietly.

What happens in each engine

SELECT customer_id, customer_name, SUM(amount)
FROM orders
GROUP BY customer_id;

Here is what each engine does with it.

• Postgres, SQL Server, BigQuery, Snowflake: error.
• MySQL: depends on the SQL mode. Since MySQL 5.7.5 the mode ONLY_FULL_GROUP_BY is on by default, so you get an error. In older setups, or if the mode was turned off, MySQL runs the query and returns an arbitrary name from the group. That is the dangerous behaviour: the number looks fine and the name may differ between runs.
• Postgres has one exception. If you group by the primary key of a table, the other columns of that table are allowed, because they are functionally dependent on the key (one key, one row, one name).

Correct ways to write it

Add the column to GROUP BY if it is really part of the grain:

GROUP BY customer_id, customer_name

Or choose the value on purpose:

SELECT customer_id,
       MAX(customer_name) AS customer_name,  -- any is fine, but be explicit
       SUM(amount)
FROM orders
GROUP BY customer_id;

Several engines (MySQL, BigQuery, Snowflake, Databricks) also have ANY_VALUE(col). It says out loud "I do not care which one", which is honest and cheaper than a MAX that has to compare every value.

Why it matters

If you feel the urge to select a non-grouped column, ask whether the data has two values for it. If it does, you have a modelling or join problem, and the real fix is upstream. Silencing the error with ANY_VALUE hides it.