Search⌘ K
AI Features
Home
Courses
Practical Data Analysis with SQL

Grouping

Explore how to use SQL grouping techniques to aggregate data by one or multiple columns, generate subtotals, and filter aggregated results using HAVING. This lesson helps you write efficient queries leveraging column aliases and positions to improve productivity and produce detailed analytical reports.

We'll cover the following...

Overview

When working with big data, it is useful to aggregate results at different levels. For example, counting the number of students per class, finding the maximum price for each category, and so on. In this lesson, we’ll use SQL to group results and compute aggregates.

Simple grouping

Let’s suppose we have a table of employees. For each employee, we keep their name, role, department, and their salary. The table looks like this:

PostgreSQL
CREATE TABLE emp AS 
    SELECT * 
    FROM (VALUES
        ('Haki',    'R&D',      'Manager',   10000),
        ('Dan',     'R&D',      'Developer', 7000),
        ('Jax',     'R&D',      'Developer', 7500),
        ('George',  'Sales',    'Manager',   8500),
        ('Bill',    'Sales',    'Developer', 8000),
        ('David',   'Sales',    'Developer', 8000)
    ) AS t(
        name,       department,  role,       salary
    );
SELECT * FROM emp;

To count the number of employees in each department, we can use the GROUP BY clause:

PostgreSQL
SELECT 
    department, 
    COUNT(*) AS number_of_employees
FROM
    emp
GROUP BY 
    department;

To get the number of employees in each department, we added the department column to the GROUP BY clause. This tells the database that we want to group the results by the value in the department column. To count the number of employees in each department, we add the aggregate expression COUNT(*) ...