GROUP BY Clause in SQL | The Data Analytics Notes

After learning about Aggregate Functions, the next step is to understand how we can perform calculations on specific groups of data rather than the entire table.

This is done using the GROUP BY clause.

What is GROUP BY?

The GROUP BY clause is used to group rows that have the same values in a specified column.

Once the data is grouped, aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() can be applied to each group.

Think of it as dividing data into categories and then performing calculations for each category separately.

Syntax

SELECT column_name, aggregate_function(column_name)

FROM table_name

GROUP BY column_name;

Example 1: Counting Employees in Each Department

SELECT dept_id, COUNT(employee_id)

FROM Employees

GROUP BY dept_id;

Explanation:

This groups employees based on their dept_id and counts the number of employees in each department.

Example 2: Total Salary by Department

SELECT dept_id, SUM(salary)

FROM Employees

GROUP BY dept_id;

Explanation:

This groups employees by department and calculates the total salary paid in each department.

Example 3: Average Salary by Department

SELECT dept_id, AVG(salary)

FROM Employees

GROUP BY dept_id;

Explanation:

This groups employees by department and calculates the average salary for each department.

Important Note

When using GROUP BY:

Every column in the SELECT statement must either be:

Included in the GROUP BY clause, or

Used inside an aggregate function

For example:

SELECT dept_id, COUNT(employee_id)

FROM Employees

GROUP BY dept_id;

Takeaway

The GROUP BY clause helps organize data into groups and perform calculations on each group separately.

It is commonly used with aggregate functions to answer questions such as:

How many employees are in each department?

What is the total salary paid by each department?

What is the average salary in each department?

This makes GROUP BY a powerful tool for data analysis and reporting.

Next Up

Now that we understand how to group data, the next step is to learn how to sort query results using the ORDER BY clause.