Why GROUP BY Disallows Non‑Aggregated Columns and How SQL Modes Influence It

GROUP BY Column Restrictions

Standard SQL only allows three kinds of expressions in the SELECT clause of an aggregated query: the grouping keys, aggregate functions (such as SUM, AVG), and constants.

Example table tbl_student_class and data:

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto‑increment primary key',
  sno varchar(12) NOT NULL COMMENT 'student number',
  cno varchar(5) NOT NULL COMMENT 'class number',
  cname varchar(20) NOT NULL COMMENT 'class name',
  PRIMARY KEY (id)
) COMMENT='student class table';
INSERT INTO tbl_student_class VALUES ('1','20190607001','0607','Film Class 7');
INSERT INTO tbl_student_class VALUES ('2','20190607002','0607','Film Class 7');
INSERT INTO tbl_student_class VALUES ('3','20190608003','0608','Film Class 8');
INSERT INTO tbl_student_class VALUES ('4','20190608004','0608','Film Class 8');
INSERT INTO tbl_student_class VALUES ('5','20190609005','0609','Film Class 9');
INSERT INTO tbl_student_class VALUES ('6','20190609006','0609','Film Class 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;

If we omit cname from the GROUP BY clause, MySQL (with ONLY_FULL_GROUP_BY enabled) returns an error:

[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '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 modes, controlled by the sql_mode system variable. DBAs set a global mode, while applications may set a session mode.

Modes affect supported syntax and data‑validation checks. Common modes include:

ONLY_FULL_GROUP_BY

ANSI_QUOTES

PIPES_AS_CONCAT

NO_TABLE_OPTIONS

NO_AUTO_CREATE_USER

NO_ZERO_DATE

NO_ZERO_IN_DATE

NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES

Only ONLY_FULL_GROUP_BY restricts that columns in SELECT must appear in GROUP BY or be aggregated.

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

When ONLY_FULL_GROUP_BY is disabled (relaxed mode), the query without cname in GROUP BY executes successfully:

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

Why Aggregated Queries Cannot Reference Non‑Grouped Columns

After GROUP BY, the result set represents groups (sets of rows), not individual rows. Columns that belong to individual rows, such as cname, are undefined for the group unless they are aggregated, so they cannot be selected directly.

This mirrors logical hierarchy: WHERE operates on rows (0‑order), while HAVING operates on groups (1‑order).

Single‑Element Sets Are Still Sets

In set theory, a single‑element set {a} is distinct from the element a. SQL, built on set theory, respects this distinction, which is why predicates like EXISTS (operating on sets) differ from row‑level predicates.

Summary

SQL strictly distinguishes hierarchy levels, both in predicate logic and set theory.

Attributes applicable to individual rows do not apply to groups, explaining the SELECT restriction after GROUP BY.

Single‑element sets share the same properties as larger sets and must be treated as sets.

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