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

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'?

CodePudding user response：

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...

CodePudding user response：

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.

CodePudding user response：

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.