MySQL error Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column solution

Error reason

Using the GROUP BY statement violates sql_mode=only_full_group_by. In MySQL database version 5.7 or above, the ONLY_FULL_GROUP_BY SQL mode is enabled by default. In this mode, for the group by operation, if the query column in the select statement is not in the group by appears, then this SQL is illegal because the column is not in the group by statement, so the database with sql_mode=only_full_group_by will report an error when using group by. In other words, the selection list, HAVING condition or ORDER BY list reference will be rejected. Queries for nonaggregate columns that are neither named in the GROUP BY clause nor functionally dependent on (uniquely determined) GROUP BY columns.

Note

It does not mean that SELECT xx, xx must be a column in GROUP BY. For example, the SELECT aggregate function (columns not in GROUP) can also be executed normally

For example

There are currently two tables: Student table and SC table (course selection table).

Execute statement

select cno,count(sc.sno),count(student.sno) from student,sc group by cno;

In the red part, you can find that neither sc.sno nor student.sno is in the GROUP BY column, but the execution statement is still normal because an aggregate function is used.

If you don’t put sc.sno in the aggregate function, an execution error will occur.

select cno,sc.sno from student,sc group by cno;

Solution

Temporary solution (invalid after restarting mysqld)

1. Make settings directly at the mysql-cli level

select @@global.sql_mode;

2. Remove ONLY_FULL_GROUP_BY from sql_mode:

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3. Restart the MySQL client

Be careful not to close mysqld, otherwise the changes will be restored.

Modify sql_mode at the MySQL-server level

It takes effect permanently and will still be effective after restarting.

Optimize mysql statement

Query using derived tables

Example

BOOKSTORAGE table:

Book deposit (book number, book title, publisher, edition, publication date, author, book price, purchase price, quantity)

CREATE TABLE BOOKSTORAGE(
isbn VARCHAR(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
publisher VARCHAR(10),
edition VARCHAR(10),
date DATE,
writer VARCHAR(10),
price FLOAT CHECK(price>=0),
pprice FLOAT CHECK(pprice>=0),
number INT CHECK(number>=0)
);

Sales (date, ISBN, quantity, unit price)

CREATE TABLE SALE(
date DATE,
isbn VARCHAR(10),
number INT CHECK(number>=0),
price FLOAT CHECK(price>=0),
FOREIGN KEY(isbn) REFERENCES BOOKSTORAGE(isbn)
);

Be sure to create the BOOKSTORAGE table first and then the SALE table. Because the foreign key of the SALE table refers to BOOKSTORAGE, BOOKSTORAGE must be created first

Insert data

INSERT
INTO BOOKSTORAGE
VALUES('1','Journey to the West','People's Literature Publishing House','Third Edition','2003/1/1','Wu Cheng'en',20.1,15.5,150),
('2','Water Margin','People's Literature Publishing House','Second Edition','2005/9/1','Shi Naian',23.9,17.1,200),
('3','Romance of the Three Kingdoms','Zhonghua Book Company Press','Fifth Edition','2008/6/1','Luo Guanzhong',26.3,15.8,210),
('4','A Dream of Red Mansions','People's Literature Publishing House','Fourth Edition','2001/6/1','Cao Xueqin',22.3,17.2,190);

INSERT
INTO SALE
VALUES('2023-3-12','1','30',17.8),
('2023-3-15','1','20',20.1),
('2023-3-12','2','25',18.8),
('2023-3-15','2','25',23.9),
('2023-3-12','3','15',18.6),
('2023-3-15','3','30',26.3),
('2023-3-12','4','22',19.5),
('2023-3-15','4','12',22.3);

Please insert BOOKSTORAGE first, because SALE refers to BOOKSTORAGE

Insert results

Solve the problem

Lists sales reports for all dates, including title, quantity, and total amount (total sales amount for each type of book)

Method 1 Modify sql_mode

mysql statement

SELECT SALE.date,BOOKSTORAGE.name,SALE.number,ROUND(SUM(SALE.price*SALE.number),2) money
FROM SALE,BOOKSTORAGE
WHERE SALE.isbn=BOOKSTORAGE.isbn
GROUP BY SALE.isbn,SALE.DATE
ORDER BY SALE.date;

Query all sql_mode

select @@global.sql_mode;

Remove ONLY_FULL_GROUP_BY

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

After restarting mysql, execute the statement

Query all sql_mode

Found no ONLY_FULL_GROUP_BY

After restarting mysqld, query sql_mode again

Found ONLY_FULL_GROUP_BY

Method 2 optimize mysql statement

SELECT SALE2.DATE,name,SALE2.number,SALE2.PRICE
FROM BOOKSTORAGE,(SELECT isbn,date,SUM(number),ROUND(SUM(price*number),2) FROM SALE GROUP BY SALE.isbn,SALE.DATE) AS SALE2(isbn,date,number,price)
WHERE SALE2.isbn=BOOKSTORAGE.isbn
ORDER BY SALE2.DATE ASC;

Thinking

Question

student table structure

When executing mysql statement, select selects more than just sno columns. Why is it output normally?

select * from student group by sno;

Answer

Because the sno attribute is the main key, that is, the primary key, after testing, it was found that group by **, and when ** is the attribute name of the primary key, the query column in the select statement does not appear in the group by, which is also allowed.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry-level skills treeHomepageOverview 77717 people are learning the system