SQL GROUP BY

The SQL GROUP BY clause is used to arrange identical data into groups based on one or more columns. It is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group of data.

Example: First, we will create a demo SQL database and table, on which we will use the GROUP BY command.

Query:

SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;

Output:

Syntax:

SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1, column2;

• aggregate_function: function used for aggregation, e.g., SUM(), AVG(), COUNT().
• table_name: name of the table from which data is selected.
• condition: Optional condition to filter rows before grouping (used with WHERE).
• column1, column2: Columns on which the grouping is applied.

Examples of GROUP BY

Let's assume that we have a Student table. We will insert some sample data into this table and then perform operations using GROUP BY to understand how it groups rows based on a column and aggregates data.

Example 1: Group By Single Column

When we group by a single column, rows with the same value in that column are combined. For example, grouping by subject shows how many students are enrolled in each subject.

Query:

SELECT subject, COUNT(*) AS Student_Count FROM Student GROUP BY subject;

Output:

Explanation: Each subject appears twice in the table, so the count for English, Mathematics and Science is 2.

Example 2: Group By Multiple Columns

Using GROUP BY with multiple columns groups rows that share the same values in those columns. For example, grouping by subject and year will combine rows with the same subject–year pair and we can count how many students fall into each group.

Query:

SELECT subject, year, COUNT(*) FROM Student GROUP BY subject, year;

Output:

Explantion: Students with the same subject and year are grouped together. Since each subject–year pair occurs twice, the count is 2 for every group.

HAVING Clause in GROUP BY Clause

HAVING clause is used to filter results after grouping, especially when working with aggregate functions like SUM(), COUNT() or AVG(). Unlike WHERE, it applies conditions on grouped data.

Example 1: Filter by Total Salary

In this query, we group employees by name and display only those whose total salary is greater than 50,000.

SELECT NAME, SUM(sal) FROM Emp GROUP BY name HAVING SUM(sal)>50000; 

Output

Explanation: Only employees whose total salary exceeds 50,000 appear in the result.

Example 2: Filter by Average Salary

In this query, we group employees by age and display only those age groups where average salary is above 60,000.

SELECT age, AVG(sal) AS Average_Salary FROM emp GROUP BY age HAVING AVG(sal) > 60000;

Output:

Explanation: This query groups employees by age and calculates average salary for each age. Only those age groups where average salary is greater than 60,000 are displayed.

Aggregate Functions and GROUP BY Statement in SQL

Visit Course