Why should we not use group functions with non-group fields without GROUP BY clause in MySQL SELECT query?
It is because without GROUP BY clause the output returned by MySQL can mislead. We are giving following example on the ‘Student’ table given below, to demonstrate it −
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)
As we can observe from the result of query above that it returns the output of group function COUNT(*) as the total number of rows in the table but the value ‘Gaurav’ in field ‘Name’ is misleading because we do on what basis, either it is the first value of the column or it is multiple times stored in column, MySQL returns it.
Now if we will write this query with GROUP BY clause then 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)
It can be observed from the above result set that with the help of GROUP BY clause we got a meaningful output.
Published on 19-Feb-2018 11:36:00
