GROUP BY, part 1


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.


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).

region product sales_amount
North A 100
North B 150
South A 200
South B 120
East A 80
East B 130

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:

region total_sales
North 250
South 320
East 210

Example 2: GROUP BY with AVG and COUNT

Consider the following employees table:

employee_id department job_title salary
1 HR Manager 60000
2 HR Analyst 50000
3 IT Engineer 70000
4 IT Analyst 55000
5 IT Manager 80000

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

  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
department avg_salary
HR 55000
IT 68333.33

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:

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

The output will be:

department avg_salary num_employees
HR 55000 2
IT 68333.33 3