- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Articles
- 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?
- Why do we use Group, Ring and Fields in Information Security?
- How can group functions be used in ORDER BY clause?
- Listing all rows by group with MySQL GROUP BY?
- SELECT DISTINCT vs GROUP BY in MySQL?
- How Can MySQL GROUP BY clause behave like DISTINCT clause?
- How will GROUP BY clause perform without an aggregate function?
- MySQL query to GROUP BY multiple columns
- Can we GROUP BY one column and select all data in MySQL?
- What are MySQL group functions?
- MySQL group by for separate id without using GROUP BY to remove duplicate column row?
- HAVING with GROUP BY in MySQL
- Using group by on two fields and count in MySQL?

Advertisements