Why do i need to include columns present in SELECT clause again in GROUP BY clause for my result?

Question

I am working on mySQL problem and I want to group the result according to height range of the players.

However, when i write down

SELECT
  id, --displays error, needs to be included in GROUP BY
  player_name,
  CASE 
    WHEN height <= 50 THEN '0-50'
    WHEN height BETWEEN 50 AND 70 THEN '51-70'
    WHEN height BETWEEN 71 AND 80 THEN '71-80'
    ELSE '80+'
  END 
  AS height_range
  
FROM
  benn.college_football_players
GROUP BY 
  height_range 
ORDER BY
  height_range DESC

I get an error that id should be used in group by clause.

Why is this necessary? Why can't i group by the derived column 'height_range'?

Answer 1

Execute query SELECT @@sql_mode to verify the ONLY_FULL_GROUP_BY is inside the query result.

From MySQL documentation for ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

It needs to either remove the above server setting to allow the query execution or in a more recommended way, add aggregate functions to columns not in your GROUP BY, e.g. SELECT MAX(id), GROUP_CONCAT(id), etc.

Removing the ONLY_FULL_GROUP_BY setting and running such non-standard SQL is permitted by MySQL but not recommended. Refer to documentation

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

Answer 2

Delete the GROUP BY clause.

SELECT
  id, 
  player_name,
  CASE 
    WHEN height <= 50 THEN '0-50'
    WHEN height <= 70 THEN '51-70'
    WHEN height <= 80 THEN '71-80'
    ELSE '80+'
  END AS height_range
FROM benn.college_football_players
ORDER BY height_range DESC

You are not aggregating, which is when values from multiple rows are aggregated into one value, eg SUM().

You are rendering a single height value as a single height_range value (for every row), which cold be described as "expressing height as one of a group of ranges", but is not "grouping" in the SQL sense of the word.

---

Note simplification of cases - the first case whose condition is true is used, so no need to use BETWEEN.

Answer 3

Why is this necessary? Why can't i group by the derived column 'height_range'?

You have a lot of rows with the same height_range.

You group these rows. The query must return one row for them.

Each source row have its own id, these values differs. But one output row may contain only one value. What of them? server doesn't know... and it generates an error.

Either remove id from the output list or use it in aggregate function (for example, SELECT MAX(id) AS id, ...). This instructs the server to return one definite value from all existing which is correct.

---

PS. After this fix you'll see the same problem with player_name output column.

PPS. Think about do you need in GROUP BY at all...