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.

Karthikeya Boyini

Published on 19-Feb-2018 11:36:00

• Related Questions & Answers
• How can we use group functions with non-group fields in MySQL SELECT query?
• Can we use MySQL GROUP BY clause with multiple columns like MySQL DISTINCT clause is used?
• How can we create a MySQL view with GROUP BY clause?
• Listing all rows by group with MySQL GROUP BY?
• How can group functions be used in ORDER BY clause?
• SELECT DISTINCT vs GROUP BY in MySQL?
• How Can MySQL GROUP BY clause behave like DISTINCT clause?
• MongoDB SELECT COUNT GROUP BY?
• How will GROUP BY clause perform without an aggregate function?
• What are MySQL group functions?
• MySQL query to GROUP BY multiple columns
• HAVING with GROUP BY in MySQL
• MySQL group by for separate id without using GROUP BY to remove duplicate column row?
• MySQL: update field with Group By?
• GROUP BY the number of rows returned by GROUP BY in MySQL?