In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group rows that have the same values in specified columns into summary rows. This allows you to perform aggregate operations, such as counting, summing, averaging, or finding the maximum/minimum values, on each group of rows.

Syntax

SELECT column1, aggregate_function(column2)
FROM your_table
GROUP BY column1

Example 1: GROUP BY with SUM

Consider the following sales table, which lists all the sales of two products in three separate regions (North, South, East).

Using the GROUP BY clause, we can calculate the total sales for each of these regions.

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region

The output will be:

Example 2: GROUP BY with AVG and COUNT

Consider the following employees table:

Let’s assume that we want to calculate the average salary in each department. We’d write:

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department

Using GROUP BY with multiple functions

In fact, once we’ve got our GROUP BY clause set up, we can add in multiple aggregation functions to our SELECT statement. Here’s an example using two functions: AVG and SUM:

SELECT 
  department, 
  AVG(salary) AS avg_salary, 
  COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department

The output will be: