- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we use group functions with non-group fields in MySQL SELECT query?
We have to use GROUP BY clause if we want to use group functions with non-group fields in the SELECT query. The general syntax can be as follows
SELECT group_function1,…, non-group-column1,… from table_name GROUP BY column_name;
mysql> Select COUNT(*), id from Student GROUP BY id; +----------+------+ | COUNT(*) | id | +----------+------+ | 1 | 1 | | 1 | 2 | | 1 | 15 | | 1 | 17 | | 1 | 20 | +----------+------+ 5 rows in set (0.00 sec) mysql> Select COUNT(*), address from Student GROUP BY id; +----------+---------+ | COUNT(*) | address | +----------+---------+ | 1 | Delhi | | 1 | Mumbai | | 1 | Delhi | | 1 | Shimla | | 1 | Jaipur | +----------+---------+ 5 rows in set (0.00 sec)
The field after GROUP BY clause can be different from non-group field given in SELECT query.
- Why should we not use group functions with non-group fields without GROUP BY clause in MySQL SELECT query?
- What are MySQL group functions?
- Why do we use Group, Ring and Fields in Information Security?
- Can we GROUP BY one column and select all data in MySQL?
- How can we create a MySQL view with GROUP BY clause?
- Can we use SELECT NULL statement in a MySQL query?
- Can we use MySQL GROUP BY clause with multiple columns like MySQL DISTINCT clause is used?
- MongoDB query to group several fields using aggregation framework?
- How we can group data in HTML forms?
- Group all documents with common fields in MongoDB?
- SELECT DISTINCT vs GROUP BY in MySQL?
- How can group functions be used in ORDER BY clause?
- How can I order in group but randomly with MySQL?
- Listing all rows by group with MySQL GROUP BY?
- How can we use MySQL SELECT without FROM clause?