SQL GROUP BY Statement explain with example

SQL GROUP BY statement is used to arrange identical data into groups with the help of aggregate functions such as COUNT, SUM, MAX, MIN, and AVG. This statement is often used with aggregate functions to perform calculations on each group of data.

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s);

Example

Consider a table named Employees with the following data:

Objective: Find the average salary for each department.

SQL Query using Group By

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

Explanation of Group By clause

• SELECT Department, AVG(Salary) AS AverageSalary: This part of the query selects the Department column and calculates the average salary for each department, renaming the result as AverageSalary.
• FROM Employees: This specifies the table from which to retrieve the data.
• GROUP BY Department: This groups the result set by the Department column.

Result

The query will return a result set like this:

• HR: The average salary is calculated as (5000 + 4000) / 2 = 4500.
• IT: The average salary is calculated as (6000 + 7000 + 8000) / 3 = 7000.

In summary, the GROUP BY statement is useful for summarizing data by grouping rows that have the same values in specified columns, and then performing aggregate calculations on each group.