Why shouldn't we use group functions for non-grouped fields without a GROUP BY clause in MySQL SELECT queries?

This is because without a GROUP BY clause, the output returned by MySQL can be misleading. We have given the following example in the 'Students' table given below for demonstration purpose -

mysql> Select * from Student;
+------+---------+---------+-----------+
| Id   | Name    | Address | Subject   |
+------+---------+---------+-----------+
| 1    | Gaurav  | Delhi   | Computers |
| 2    | Aarav   | Mumbai  | History   |
| 15   | Harshit | Delhi   | Commerce  |
| 20   | Gaurav  | Jaipur  | Computers |
+------+---------+---------+-----------+
4 rows in set (0.00 sec)

mysql> Select count(*), Name from Student;
+----------+--------+
| count(*) | name   |
+----------+--------+
| 4        | Gaurav |
+----------+--------+
1 row in set (0.00 sec)

From the above query result we can observe that it returns the output of the group function COUNT(*) as table but the value "Gaurav" in the field "Name" is misleading because on what basis we are doing this, either it is the first value of the column or it is stored in the column multiple times, MySQL returns it .

Now, if we write this query using GROUP BY clause, the result set is as follows -

mysql> Select count(*), name from student GROUP BY id;
+----------+---------+
| count(*) | name    |
+----------+---------+
| 1        | Gaurav  |
| 1        | Aarav   |
| 1        | Harshit |
| 1        | Gaurav  |
+----------+---------+
4 rows in set (0.00 sec)

As can be seen from the above result set, with the help of GROUP BY clause, We get meaningful output.

The above is the detailed content of Why shouldn't we use group functions for non-grouped fields without a GROUP BY clause in MySQL SELECT queries?. For more information, please follow other related articles on the PHP Chinese website!