How to use GROUP BY in SQL?
The GROUP BY clause in sql is used to group the rows that have identical values by one or more columns. In this article, we will briefly discuss how GROUP BY clause is used in SQL with aggregation function, having clause, order by clause and with the case statement.
In the previous article, we have discussed SELECT statement, ORDER BY, HAVING clause, and aggregation functions in SQL. Now, in this article, we will discuss how to group the identical values of a column present in different rows.
Table of Content
- What is GROUP BY in SQL?
- COUNT with GROUP BY
- HAVING with GROUP BY
- ORDER BY with GROUP BY
- CASE with GROUP BY
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is GROUP BY in SQL?
GROUP BY in SQL is used to group all the rows that have the same (or identical) value by one or more columns.
- It is used in conjunction with the aggregate function
- It follows WHERE clause in SELECT statement and precedes the ORDER BY clause.
The below image shows how the GROUP BY clause works in SQL.
The above table (left table) contains the record of Employees (Employee ID, Name, and Department), and when we apply the GROUP BY clause on the Department column, it returns the result set that includes the unique value in the department column (Sales, Marketing, and Education).
SELECT column_namesFROM table_nameWHERE conditionsGROUP BY column_namesORDER BY column_names;
Now, we will take some examples to understand how to use the GROUP BY clause in SQL.
Let’s have a Student table that contains the StudentID, Name, Percentage, and corresponding Grade.
| Student ID | Name | Percentage | Grade | Remark |
| 1001 | Ajay | 87 | A | Excellent |
| 1002 | Babloo | 81 | A | Excellent |
| 1003 | Chhavi | 79 | B | Good |
| 1004 | Dheeraj | 93 | O | Excellent |
| 1005 | Evina | 95 | O | Excellent |
| 1006 | Fredy | 80.7 | A | Excellent |
| 1007 | Garima | 63 | C | Poor |
| 1008 | Hans | 49 | F | Fail |
| 1009 | Ivanka | 88 | A | Excellent |
| 1010 | Jai | 74 | B | Good |
| 1011 | Kundan | 88 | A | Excellent |
| 1012 | Himanshi | 37 | F | Fail |
| 1013 | Atul | 67 | B | Good |
| 1014 | Jaya | 48 | F | Fail |
| 1015 | Aquib | 75 | B | Good |
Must Read: Introduction to SQL
Must Read: UPDATE query in SQL
COUNT with GROUP BY
Example: Use the GROUP BY clause to count the number of students by the grade.
Query
SELECT Grade, COUNT (StudentID) AS Number of StudentsFROM StudentGROUP BY Grade;
Output
| Grade | Number of Students |
| A | 5 |
| B | 4 |
| C | 1 |
| F | 3 |
| O | 2 |
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views
Must Read: SQL Logical Operator
GROUP BY with HAVING
Example: Find the grade in which the number of students is greater than or equal to 3.
Query
SELECT Grade, COUNT (StudentID) AS Number of StudentsFROM StudentGROUP BY GradeHAVING Number of Students >= 3;
Output
| Grade | Number of Students |
| A | 5 |
| B | 4 |
| F | 3 |
Must Read: Introduction to Normalization
Must Read: SQL HAVING
GROUP BY with ORDER BY
Example: Use the ORDER BY clause in the above example to arrange the number of students in each grade in descending order.
Query
SELECT Grade, COUNT (StudentID) AS Number of StudentsFROM StudentGROUP BY GradeORDER BY Number of Students DESC;
Output
| Grade | Number of Students |
| A | 5 |
| B | 4 |
| F | 3 |
| O | 2 |
| C | 1 |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
GROUP BY with CASE
Example: Classify the students in two (Pass or Fail) and count the number of student in both the category.
Query
SELECT CASE WHEN Percentage > 40 THEN ‘PASS’ ELSE ‘FAIL’ END AS Category, COUNT (*) AS Number of StudentsFROM StudentGROUP BY Grade;
Output
| Category | Number of Students |
| PASS | 14 |
| FAIL | 1 |
Must Read: SQL CASE statement
Must Check: Database and SQL Online Course and Certifications
Conclusion
In this article, we have briefly discussed how to use GROUP BY in SQL with the help of examples.
Hope this article, will help you in your Data Science/Data Analysis journey.
Keep Learning!!
Keep Sharing!!
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
