Sửa lỗi ERROR 1111: Invalid use of group function - Dùng aggregate đúng với WHERE

Lỗi ERROR 1111 (HY000): Invalid use of group function xảy ra khi dùng hàm tập hợp (AVG, SUM, COUNT...) trực tiếp trong mệnh đề WHERE. Giải pháp: dùng subquery.

❌ Sai (gây lỗi 1111)

SELECT * FROM EmployeeInformation WHERE EmployeeSalary > AVG(EmployeeSalary);

✅ Đúng (dùng subquery)

SELECT * FROM EmployeeInformation 
WHERE EmployeeSalary > (SELECT AVG(EmployeeSalary) FROM EmployeeInformation);

Ví dụ minh họa

Tạo bảng nhân viên:

mysql> CREATE TABLE EmployeeInformation (
    -> EmployeeId INT,
    -> EmployeeName VARCHAR(20),
    -> EmployeeSalary INT,
    -> EmployeeDateOfBirth DATETIME
    -> );

mysql> INSERT INTO EmployeeInformation VALUES(101, 'John', 5510, '1995-01-21');
mysql> INSERT INTO EmployeeInformation VALUES(102, 'Carol', 5600, '1992-03-25');
mysql> INSERT INTO EmployeeInformation VALUES(103, 'Mike', 5680, '1991-12-25');
mysql> INSERT INTO EmployeeInformation VALUES(104, 'David', 6000, '1991-12-25');
mysql> INSERT INTO EmployeeInformation VALUES(105, 'Bob', 7500, '1993-11-26');

Dữ liệu:

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        101 | John         |           5510 | 1995-01-21 00:00:00 |
|        102 | Carol        |           5600 | 1992-03-25 00:00:00 |
|        103 | Mike         |           5680 | 1991-12-25 00:00:00 |
|        104 | David        |           6000 | 1991-12-25 00:00:00 |
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+

Lương trung bình: (5510+5600+5680+6000+7500)/5 = 6058

Tìm nhân viên lương > trung bình:

mysql> SELECT * FROM EmployeeInformation 
    -> WHERE EmployeeSalary > (SELECT AVG(EmployeeSalary) FROM EmployeeInformation);

Kết quả (chỉ Bob 7500 > 6058):

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+

Quy tắc nhớ

• WHERE: Lọc hàng **trước** khi gom nhóm → không dùng aggregate
• HAVING: Lọc nhóm **sau** khi gom nhóm → dùng aggregate
• Cần aggregate trong WHERE → bọc trong subquery