Why SELECT Columns Not in GROUP BY Cannot Be Referenced After Aggregation in MySQL

Restriction on SELECT Columns After GROUP BY

The SQL standard permits only three kinds of expressions in the SELECT list of an aggregated query: the grouping keys specified by GROUP BY , aggregate functions such as SUM or AVG , and constants.

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班'),
('2','20190607002','0607','影视7班'),
('3','20190608003','0608','影视8班'),
('4','20190608004','0608','影视8班'),
('5','20190609005','0609','影视9班'),
('6','20190609006','0609','影视9班');

Typical Query and Error

Attempting to count students per class while also selecting cname without including it in GROUP BY leads to an error:

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

MySQL returns: [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_student_class.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQL Modes

MySQL can run under different sql_mode settings, which affect syntax support and data validation. The ONLY_FULL_GROUP_BY mode enforces the rule that any column in the SELECT list must either appear in GROUP BY or be aggregated.

Other common modes include ANSI_QUOTES , PIPES_AS_CONCAT , NO_AUTO_CREATE_USER , NO_ENGINE_SUBSTITUTION , and STRICT_TRANS_TABLES . Their effects range from quoting behavior to strict handling of invalid data.

Default Modes

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

When ONLY_FULL_GROUP_BY is disabled (relaxed mode), the earlier query executes successfully, but relying on such configuration is discouraged for production environments.

Conceptual Explanation

SQL is a set‑oriented language; GROUP BY transforms rows (0‑order objects) into groups (1‑order objects). Attributes that belong to individual rows (e.g., cname ) are not meaningful for a group unless aggregated.

Using a non‑aggregated column after grouping is a category error analogous to applying a property of an element to a set of elements.

Summary

1. SQL strictly distinguishes levels such as individual rows and groups. 2. Because of this distinction, columns that describe single rows cannot be directly referenced after aggregation without aggregation. 3. Understanding SQL modes and set theory helps avoid errors and write portable queries.