Why SELECT Columns After GROUP BY Are Restricted: MySQL Modes Explained

GROUP BY Column Restrictions

Standard SQL allows only three kinds of expressions in the SELECT list of an aggregated query: the grouping keys, aggregate functions (SUM, AVG, etc.), and constants. Below is an example table and data.

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(20) NOT NULL COMMENT '班级名',
  PRIMARY KEY (id)
) COMMENT='学生班级表';
INSERT INTO tbl_student_class VALUES ('1','20190607001','0607','影视7班');
INSERT INTO tbl_student_class VALUES ('2','20190607002','0607','影视7班');
INSERT INTO tbl_student_class VALUES ('3','20190608003','0608','影视8班');
INSERT INTO tbl_student_class VALUES ('4','20190608004','0608','影视8班');
INSERT INTO tbl_student_class VALUES ('5','20190609005','0609','影视9班');
INSERT INTO tbl_student_class VALUES ('6','20190609006','0609','影视9班');

We want to count students per class and get the maximum student number:

SELECT cno,cname,COUNT(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno,cname;

Someone may think that because cno uniquely determines cname, the query could be simplified:

SELECT cno,cname,COUNT(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno;

This fails with error 1055 because cname is not functionally dependent on the GROUP BY columns under the ONLY_FULL_GROUP_BY mode.

SQL Modes

MySQL can run under different SQL modes, controlled by the sql_mode system variable. Modes affect syntax support and data‑validation checks, making MySQL adaptable to various environments.

Syntax Support Modes

ONLY_FULL_GROUP_BY

– disallows non‑aggregated columns that are not in the GROUP BY clause. ANSI_QUOTES – treats double quotes as identifier delimiters. PIPES_AS_CONCAT – interprets || as string concatenation. NO_TABLE_OPTIONS – omits engine‑specific options in SHOW CREATE TABLE. NO_AUTO_CREATE_USER – disables automatic user creation with GRANT.

Data‑Check Modes

NO_ZERO_DATE

– treats ‘0000‑00‑00’ as illegal. NO_ENGINE_SUBSTITUTION – throws an error when the requested storage engine is unavailable. STRICT_TRANS_TABLES – enables strict mode for INSERT / UPDATE.

The default mode for MySQL 5.7.21 includes:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Removing ONLY_FULL_GROUP_BY allows the simplified query to run, but relying on a lax mode is discouraged in production because it can return nondeterministic values when cno and cname are not strictly one‑to‑one.

Why Columns from the Original Table Cannot Be Referenced After Aggregation

Aggregated queries operate on sets of rows; attributes that belong to individual rows (e.g., cname) are not meaningful for the whole group. GROUP BY turns rows into a collection, so only aggregate results or grouping keys are valid in the SELECT list.

This mirrors set‑theoretic concepts: a single‑element set is still a set, and SQL distinguishes between row‑level predicates ( WHERE) and set‑level predicates ( HAVING).

Summary

SQL strictly separates levels, including predicate logic and set theory.

Attributes that apply to individual rows cannot be used directly after aggregation.

Even a single‑element set must be treated as a set to preserve theoretical consistency.

References: “SQL Fundamentals”, “SQL Advanced Tutorial”.