
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- Resolve ERROR 1111 (HY000): Invalid use of group function in MySQL? How to correctly use aggregate function with where clause?
- 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?
- SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING
- How to add column values in MySQL without using aggregate function?
- What is the benefit of using MySQL SUM() function with GROUP BY clause?
- How can group functions be used in ORDER BY clause?
- How can I aggregate collection and group by field count in MongoDB?
- 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?
- How can we create a MySQL view with GROUP BY clause?
- How to order or choose rows in MySQL GROUP BY clause?
- How to perform conditional GROUP BY in MySQL to fetch?
- Using an Aggregate function in SAP HANA
- MongoDB Aggregate group multiple result?

Advertisements