Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Resolved: Non-Aggregated - Group By
An error occurs if all fields are non-aggregated, and each of these fields must be included in the
Data in the video:
Data after applying
GROUP BY clause. Additionally, the data shown in the video is different from the data that has been grouped based on non-aggregated fields. This issue also appears in other SQL courses, where similar errors and data discrepancies frequently occur. Why does this happen?Data in the video:
Data after applying
GROUP BY :
4 answers ( 1 marked as helpful)
Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
This issue is related to the SQL mode
ONLY_FULL_GROUP_BY in MySQL.When
ONLY_FULL_GROUP_BY is enabled (which is the default in recent versions of MySQL), every column in your SELECT list must either:- Be part of the
GROUP BY clause, or- Be used inside an aggregate function (like SUM(), AVG(), COUNT(), etc.)
Why the Video Shows Different Data
Many tutorials and video courses either:
- Use older MySQL versions (before
ONLY_FULL_GROUP_BY was enforced), or- Have
ONLY_FULL_GROUP_BY disabled in their SQL server config.In those environments, MySQL allows the query but picks arbitrary values for non-grouped, non-aggregated columns - which can produce inconsistent or incorrect results.
That's why:
- The data in the video looks fine, but
- When you try the same query in your MySQL (with default settings), you get an error or different output.
How to Fix the Error
Turn Off
ONLY_FULL_GROUP_BY (not recommended for production)Run this in SQL:
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'; SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'; Note:
GLOBAL changes apply to new connections only.
SESSION changes apply to your current connection only.
Hi, Nurkholiq,
Thanks for sharing. When I tried to load dataset(growth.sql) to mysql I get an error "[WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\User\\AppData\\Local\\Temp\\tmpdgvlt8p3.cnf'" do you know how can I solve it?
Thanks for sharing. When I tried to load dataset(growth.sql) to mysql I get an error "[WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\User\\AppData\\Local\\Temp\\tmpdgvlt8p3.cnf'" do you know how can I solve it?
Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Hi, Aysel
This error usually happens when:
- Another program like MySQL Workbench or another script is currently using the file
- or you're trying to import the
Growth_Data.sql file while it’s still open in another applicationHere are a couple of things you can try:
- Restart MySQL Workbench (close all open tabs, then reopen and try again) or
- Reboot your computer
Hope this helps!
Thanks a lot for your support, Nurkholiq. I could finally fix the problem