How to use GROUP BY in SQL?

# How to use GROUP BY in SQL?

3 mins read517 Views Comment
clickHere
Vikram Singh
Assistant Manager - Content
Updated on Mar 31, 2023 16:40 IST

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.

## 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.

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).

Must Read: SQL DELETE

Must Read: SQL ALTER TABLE

Syntax

` `
`SELECT column_namesFROM table_nameWHERE conditionsGROUP BY column_namesORDER BY column_names;Copy code`

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.

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;Copy code`

Output

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;Copy code`

Output

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;Copy code`

Output

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;Copy code`

Output

Must Read: SQL CASE statement

## Conclusion

In this article, we have briefly discussed how to use GROUP BY in SQL with the help of examples.

Keep Learning!!

Keep Sharing!!

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties