How will GROUP BY clause perform without an aggregate function?

When we use GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like DISTINCT clause. For example, we have the following table −

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
| 132  | Shyam   | Chandigarh | Economics  |
| 133  | Mohan   | Delhi      | Computers  |
| 150  | Saurabh | NULL       | Literature |
+------+---------+------------+------------+
7 rows in set (0.00 sec)

By using the DISTINCT clause on column ‘Address’, MySQL returns the following result set.

mysql> Select DISTINCT ADDRESS from Student_info;
+------------+
| ADDRESS    |
+------------+
| Amritsar   |
| Chandigarh |
| Shimla     |
| Jhansi     |
| Delhi      |
| NULL       |
+------------+
6 rows in set (0.07 sec)

Now, by using GROUP BY clause as follows, we can get the same result set as we got by using DISTINCT −

mysql> Select ADDRESS from Student_info GROUP BY Address;
+------------+
| ADDRESS    |
+------------+
| NULL       |
| Amritsar   |
| Chandigarh |
| Delhi      |
| Jhansi     |
| Shimla     |
+------------+
6 rows in set (0.00 sec)

We can observe a difference between both the result sets returned by MySQL that the result set returns by MySQL query using GROUP BY clause is sorted and in contrast, the result set return by MySQL query using DISTICT clause is not sorted.

Krantik Chavan

Updated on 22-Jun-2020 12:31:12

• Related Questions & Answers
• How Can MySQL GROUP BY clause behave like DISTINCT clause?
• Why should we not use group functions with non-group fields without GROUP BY clause in MySQL SELECT query?
• Resolve ERROR 1111 (HY000): Invalid use of group function in MySQL? How to correctly use aggregate function with where clause?
• SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING
• How can group functions be used in ORDER BY clause?
• What is the benefit of using MySQL SUM() function with GROUP BY clause?
• How to add column values in MySQL without using aggregate function?
• How can I aggregate collection and group by field count in MongoDB?
• How can we create a MySQL view with GROUP BY clause?
• How to order or choose rows in MySQL GROUP BY clause?
• MongoDB Aggregate group multiple result?
• How to perform conditional GROUP BY in MySQL to fetch?
• What is the SQL query describing usage of MAX aggregate function and GROUP-BY with HAVING?
• How can we know the repetition of a value in column with the help of group function COUNT(*) and GROUP BY clause?
• Using an Aggregate function in SAP HANA