SQL COUNT() with GROUP BY Clause

The SQL COUNT() function is a powerful tool used to count the number of rows in a dataset. When combined with the GROUP BY clause, it helps group data by specific attributes and count rows within each group. This is particularly useful for summarising data and generating insights.

In this article, we will explain how to use the COUNT() function with the GROUP BY clause, understand its syntax, and examine examples to apply it effectively in SQL queries.

COUNT() with GROUP BY Clause

The COUNT() function with the GROUP BY clause is a powerful combination for grouping data based on specific attributes and counting rows within each group. It helps to summarise datasets and generate meaningful insights by categorizing data into logical groups. The syntax for using the COUNT() function with the GROUP BY clause is straightforward and easy to implement: This syntax enables us to summarise data and perform efficient grouping and counting.

Syntax:

SELECT attribute1 , COUNT(attribute2)
FROM table_name
GROUP BY attribute1

Key Terms:

• attribute1: The column by which the rows will be grouped.
  
• COUNT(attribute2): Counts the number of rows for each group.
  
• table_name: The name of the table where the data resides.
  
• GROUP BY: Groups the rows based on attribute1.

Example of SQL COUNT() with GROUP BY Clause

To demonstrate how to use the COUNT() function with the GROUP BY clause, we will create a sample student_marks table containing student information, such as ID, name, branch, and total marks, and then group and count students by their branch.

Query:

CREATE DATABASE GeeksforGeeks;
USE GeeksforGeeks;
CREATE TABLE student_marks(
stu_id VARCHAR(20),
stu_name VARCHAR(20),
stu_branch VARCHAR(20),
total_marks INT
);

INSERT INTO student_marks
VALUES( '1001','PRADEEP','E.C.E', 550),
( '1002','KIRAN','E.C.E', 540),
( '1003','PRANAV','E.C.E', 450),
( '2001','PADMA','C.S.E', 570),
( '2002','SRUTHI','C.S.E', 480),
( '2003','HARSITHA','C.S.E', 534),
( '3001','SAI','I.T', 560),
( '3002','HARSH','I.T', 510),
( '3003','HARSHINI','I.T', 500);

Select * FROM student_marks;

Output:

Example 1

In this example, we will find the number of students in each branch using the COUNT() function and GROUP BY clause. The stu_branch column is used to group the data by student branch, ensuring that all rows with the same branch value are grouped together

Query:

SELECT stu_branch, COUNT(stu_id) AS number_of_students
FROM student_marks
GROUP BY stu_branch

Output:

Example 2

In this example, we will count number of students whose total marks are greater than the average marks of all students in the table. The query first uses a subquery to calculate the average total marks across all students. This average is then used in the WHERE clause to filter rows where a student’s total marks are greater than the calculated average. The COUNT(stu_id) function counts the number of such students, while the main query also returns the average marks for reference.

Query:

SELECT AVG(total_marks) AS average,COUNT(stu_id) AS number_of_students
FROM student_marks
WHERE total_marks>(SELECT AVG(total_marks) FROM student_marks)

Output:

Conclusion

The COUNT() function, when combined with the GROUP BY clause, is an essential tool for aggregating and summarising data in SQL. Whether we want to count rows based on specific attributes or perform advanced queries involving subqueries and conditions, mastering these techniques is invaluable. By understanding and applying these examples, we can manage and analyse data more effectively in SQL.