Group and Aggregate Data with SQL in PostgreSQL

Table of Contents

1. Module 1 — Summarizing data with aggregation functions (13m 22s)

• 1.1 Introduction to aggregation in PostgreSQL
• 1.2 Demo: First steps with aggregation
• 1.3 Demo: Aggregation and Null Values
• 1.4 Demo: Conditional aggregation with CASE
• 1.5 Demo: Improve readability with FILTER
• 1.6 Demo: Clarify results with SQL aliases

2. Module 2 — Data grouping with GROUP BY and HAVING (12m 42s)

• 2.1 Introduction to GROUP BY and HAVING clauses
• 2.2 Demo: Metrics by category with GROUP BY
• 2.3 Demo: Grouping by several columns
• 2.4 Demo: Difference between WHERE and HAVING
• 2.5 Demo: Fix common GROUP BY errors
• 2.6 Demo: Present results with ORDER BY and LIMIT
• 2.7 Over-clustering - how it distorts the results
• 2.8 Data reshaping by clustering

3. Calculations with window functions

• 3.1 Demo: Row-level data vs grouped results
• 3.2 Demo: Basic structure of a window function
• 3.3 Demo: PARTITION BY vs GROUP BY
• 3.4 Demo: Aggregation functions in windows
• 3.5 Demo: Ranking functions
• 3.6 Demo: Window frames — window frame control
• 3.7 Demo: Aggregation with and without partition — choose the right tool

4. Module 4 — Combining aggregation and filtering for analysis (18m 11s)

• 4.1 Introduction: aggregations combined with JOINs
• 4.2 Demo: Integrate aggregations and JOIN
• 4.3 Demo: Pre-aggregation for correct results
• 4.4 Demo: Calculate metrics with GROUP BY and aggregation
• 4.5 Demo: Handle missing data with COALESCE
• 4.6 Demo: Combine GROUP BY and window functions
• 4.7 Demo: Validate the correction of results
• 4.8 Demo: Avoid results without analytical meaning

5. Appendix: Course Database

• Restore script
• sanity check

1. Summarizing Data with Aggregate Functions

1.1 Introduction to aggregation in PostgreSQL

Aggregation in SQL is not a row-by-row calculation. Aggregate functions operate on rowsets: their role is to take many rows as input and reduce them to a single scalar value.

This rowset is defined by the FROM clause, optionally filtered by WHERE, and, when introduced, partitioned by GROUP BY.

Fundamental rule: if no GROUP BY is present, PostgreSQL treats the entire result set as a single group. This is why a query with aggregate functions and no grouping always returns exactly a single row.

Logical execution order:

FROM → WHERE (filtrage) → GROUP BY → fonctions d'agrégation → HAVING → SELECT → ORDER BY → LIMIT

Rows are selected first, filtering is applied next, and only then do aggregation functions run on the remaining set. If we think of aggregates as operating on individual lines, the rest of the course will seem confusing. If we think in terms of sets, everything aligns.

1.2 Demo: Getting started with aggregation

This first demo covers the basics of aggregation in PostgreSQL from an RH table (hr.hris).

COUNT(*) — Count all lines

-- Compter le nombre total d'employés dans la table HRIS.
SELECT COUNT(*)
FROM hr.hris;

COUNT(*) counts rows, regardless of whether columns contain NULLs. It simply counts the number of records used in the table.

COUNT(column) — Count non-NULL values

-- Compter le nombre d'employés avec un salaire non-NULL.
SELECT COUNT(salary)
FROM hr.hris;

This is an important distinction. The input rows have not changed, but the aggregate rules differ: the query still operates on the same set of rows, but only counts rows where salary is not NULL.

SUM and AVG

-- Calculer la somme et la moyenne des salaires.
SELECT
    SUM(salary),
    AVG(salary)
FROM hr.hris;

MIN and MAX

-- Trouver le salaire minimum et maximum.
SELECT
    MIN(salary),
    MAX(salary)
FROM hr.hris;

Combine multiple aggregations into a single query

-- Combiner plusieurs agrégations dans une seule requête.
SELECT
    COUNT(*)        AS employee_count,
    COUNT(salary)   AS salaried_employees,
    SUM(salary)     AS total_payroll,
    AVG(salary)     AS average_salary
FROM hr.hris;

There is only one table, one set of rows, and several aggregate functions, each producing its own scalar result. Everything is crushed into a single line summary.

1.3 Demo: Aggregation and NULL values

Before we go any further, we need to be very clear about how aggregate functions handle NULL values.

In SQL, NULL does not mean zero — it means unknown. Most aggregate functions simply ignore these values:

Important consequence: Aggregates do not treat NULL as zero, but as absent. If this is not taken into account, the results can be technically correct and analytically wrong at the same time.

Check for NULL in salaries

-- Comparer le nombre total de lignes et le nombre de salaires présents.
SELECT
    COUNT(*)        AS total_rows,
    COUNT(salary)   AS salaries_present
FROM hr.hris;

Enter NULL values ​​for demonstration

-- Introduire quelques valeurs NULL dans la colonne salary pour la démonstration.
UPDATE hr.hris
SET salary = NULL
WHERE random() < 0.05;

Effect on SUM and AVG after introduction of NULL

-- SUM et AVG ignorent les NULL entièrement.
SELECT
    SUM(salary) AS total_payroll,
    AVG(salary) AS average_salary
FROM hr.hris;

See all three values ​​together

-- Montrer explicitement l'impact des NULL.
SELECT
    COUNT(*)      AS total_employees,
    COUNT(salary) AS salaried_employees,
    AVG(salary)   AS average_salary
FROM hr.hris;

The average is calculated only from employees with non-NULL salaries, not using the total number of employees.

Impact of NULLs with grouping by department

-- Les différents départements ont différents nombres de valeurs NULL.
SELECT
    department,
    COUNT(*)      AS employee_count,
    COUNT(salary) AS salaried_employees,
    AVG(salary)   AS average_salary
FROM hr.hris
GROUP BY department;

Different departments now have different numbers of missing salary values, meaning their averages are based on different denominators.

1.4 Demo: Conditional aggregation with CASE

Sometimes you don’t want to aggregate all the rows in the same way. We want to calculate different summaries depending on the conditions in the data. This is where conditional aggregation comes in.

Key concept: a CASE expression is evaluated line by line and produces a value based on a condition. When you place this expression inside an aggregation function, you can calculate conditional counts, totals, or averages in a single query.

A common pattern is to sum 1s and 0s to produce conditional counts, or to sum a numeric column only when a condition is true. If in a CASE expression there is no ELSE clause and the condition is not satisfied, the expression returns NULL — meaning that this row does not contribute to the aggregate.

Total count and absence hours

-- Calculer le nombre total de records d'absence et le total des heures absentes.
SELECT
  COUNT(*) AS rows,
  SUM(hours_absent) AS total_hours_absent
FROM hr.employee_absence;

Conditional counts with CASE

-- Calculer les employés sans absence, avec une absence, et avec une absence élevée (40+ h).
SELECT
  COUNT(*) AS employees,
  SUM(CASE WHEN hours_absent = 0 THEN 1 ELSE 0 END) AS no_absence_count,
  SUM(CASE WHEN hours_absent > 0 THEN 1 ELSE 0 END) AS any_absence_count,
  SUM(CASE WHEN hours_absent >= 40 THEN 1 ELSE 0 END) AS high_absence_count
FROM hr.employee_absence;

Conditional totals with CASE

-- Calculer le total des heures absentes et les heures d'absence élevée (40+ h).
SELECT
  SUM(hours_absent) AS total_hours_absent,
  SUM(CASE WHEN hours_absent >= 40 THEN hours_absent ELSE 0 END) AS high_absence_hours
FROM hr.employee_absence;

Grouping by department with conditional aggregation

-- Statistiques d'absence par département.
SELECT
  department,
  COUNT(*) AS employees,
  SUM(CASE WHEN hours_absent > 0 THEN 1 ELSE 0 END) AS any_absence_count,
  SUM(CASE WHEN hours_absent >= 40 THEN 1 ELSE 0 END) AS high_absence_count,
  SUM(hours_absent) AS total_hours_absent
FROM hr.employee_absence
GROUP BY department
ORDER BY total_hours_absent DESC;

CASE gives conditional logic at the row level, and aggregates give summaries of these conditions at the dataset or group level.

1.5 Demo: Improve readability with FILTER

PostgreSQL supports the FILTER clause, which allows you to apply a condition directly to an aggregate function. Instead of embedding conditional logic inside the expression, the condition is attached to the aggregate itself.

Advantages of FILTER:

• Each aggregate can have its own filter
• It is immediately clear which lines contribute to which result
• More readable, especially with multiple conditional aggregates in the same query
• At PostgreSQL this generally comes with no performance penalty

The FILTER clause is defined by the SQL standard, but it is not implemented uniformly in all database systems. PostgreSQL supports it, making it a useful and expressive option.

Important: CASE and FILTER are functionally equivalent. FILTER doesn’t change what the query does, it changes how clearly the intent is expressed.

Conditional counts with FILTER

-- Compter les employés selon leurs heures d'absence avec FILTER.
SELECT
  COUNT(*) AS employees,
  COUNT(*) FILTER (WHERE hours_absent = 0)   AS no_absence_count,
  COUNT(*) FILTER (WHERE hours_absent > 0)   AS any_absence_count,
  COUNT(*) FILTER (WHERE hours_absent >= 40) AS high_absence_count
FROM hr.employee_absence;

Conditional totals with FILTER

-- Sommer les heures d'absence selon des conditions avec FILTER.
SELECT
  SUM(hours_absent) AS total_hours_absent,
  SUM(hours_absent) FILTER (WHERE hours_absent >= 40) AS high_absence_hours
FROM hr.employee_absence;

Grouping by department with FILTER

-- Statistiques d'absence par département en utilisant FILTER.
SELECT
  department,
  COUNT(*) AS employees,
  COUNT(*) FILTER (WHERE hours_absent > 0)   AS any_absence_count,
  COUNT(*) FILTER (WHERE hours_absent >= 40) AS high_absence_count,
  SUM(hours_absent) AS total_hours_absent
FROM hr.employee_absence
GROUP BY department
ORDER BY total_hours_absent DESC;

1.6 Demo: Clarify results with SQL aliases

This section covers how aggregate results are presented. By default, aggregate functions produce column names that are generic or derived from the expression itself, which is not very useful.

Role of aliases:

• Give each result a clear, descriptive name that reflects what the value actually represents
• Does not change query execution, only improves readability
• Makes output easier to understand and reuse
• Essential when aggregation results leave the query window and end up in reports, dashboards, or downstream analyzes

Aliases aren’t just cosmetic — they make aggregate output self-describing, which is critical when queries are shared, saved, or used by other tools.

Query without alias (hard to read)

-- Requête simple sans alias.
SELECT
    COUNT(*),
    AVG(salary)
FROM hr.hris;

Query with alias (readable and self-describing)

-- Réécriture avec des alias clairs.
SELECT
    COUNT(*)      AS employee_count,
    AVG(salary)   AS average_salary
FROM hr.hris;

Conditional aggregates with aliases

-- Requête plus complexe avec plusieurs alias.
SELECT
    COUNT(*) AS employees,
    COUNT(*) FILTER (WHERE hours_absent > 0)   AS employees_with_absence,
    COUNT(*) FILTER (WHERE hours_absent >= 40) AS high_absence_employees
FROM hr.employee_absence;

2. Grouping data with GROUP BY and HAVING

2.1 Introduction to GROUP BY and HAVING clauses

This module shows how to use GROUP BY to calculate metrics by categories — for example counts and averages by department or by role — and how the filtering changes depending on whether you filter rows with WHERE before aggregation, or groups with HAVING after aggregation.

Without GROUP BY: Aggregate functions summarize the entire table into a single result (a total or an average).

With GROUP BY: PostgreSQL calculates these same aggregates separately for each defined category. This changes both the shape of the result set and the question the query answers.

This module also establishes the practical limits of GROUP BY, which explains why window functions will be needed in the next module.

2.2 Demo: Metrics by category with GROUP BY

Global aggregate (without grouping)

-- Calculer le salaire moyen de tous les employés (une seule ligne de résultat).
SELECT
    AVG(salary) AS avg_salary
FROM hr.hris;

This query returns exactly one row: this is a global metric, a single average that represents the entire dataset.

Grouping by department

-- Compter le nombre d'employés dans chaque département.
SELECT
    department,
    COUNT(*) AS employee_count
FROM hr.hris
GROUP BY department;

The GROUP BY department clause tells PostgreSQL to produce one row per department. This is very different from an ungrouped count.

Multiple aggregates per group

-- Calculer la moyenne et le total des salaires par département.
SELECT
    department,
    COUNT(*)      AS employee_count,
    AVG(salary)   AS average_salary,
    SUM(salary)   AS total_payroll
FROM hr.hris
GROUP BY department;

The grouping column defines the category, and each aggregate in the SELECT list is calculated independently within that group. Nothing in the underlying data has changed — only the grouping completely changes the shape and meaning of the result.

2.3 Demo: Grouping by several columns

Each column added to the GROUP BY clause increases the level of detail in the results. This gives a more granular overview, but also increases the number of rows returned.

Grouping by department and position

-- Compter les employés et la moyenne salariale pour chaque combinaison département/poste.
SELECT
    department,
    position,
    COUNT(*)    AS employee_count,
    AVG(salary) AS average_salary
FROM hr.hris
GROUP BY
    department,
    position;

Instead of one row per department, we now get one row for each unique department + job title pair. More detail, but also more fragmentation of the aggregates.

Rule: each column added to GROUP BY increases the granularity. More detail can be useful, but it also fragments the aggregates. It’s a powerful technique, but it’s easy to overuse.

2.4 Demo: Difference between WHERE and HAVING

These clauses may seem interchangeable at first, but they operate at different stages of query execution.

The key distinction is timing. WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Confusing them does not necessarily cause an error, but may result in a wrong answer.

Filtering rows with WHERE (before aggregation)

-- Garder seulement les employés avec un salaire >= 80 000 avant le regroupement.
SELECT
    department,
    COUNT(*)    AS employee_count,
    AVG(salary) AS average_salary
FROM hr.hris
WHERE salary >= 80000
GROUP BY department;

PostgreSQL first filters the rows, then groups the remaining data by department and calculates the aggregates. The results only reflect employees who meet the salary condition.

Filtering groups with HAVING (after aggregation)

-- Garder seulement les départements avec au moins 5 employés.
SELECT
    department,
    COUNT(*)    AS employee_count,
    AVG(salary) AS average_salary
FROM hr.hris
GROUP BY department
HAVING COUNT(*) >= 5;

PostgreSQL first groups all rows by department, calculates the aggregates, and only then applies the HAVING condition. All rows contribute to the aggregates, but only qualifying groups are returned.

HAVING follows GROUP BY in the query, and grouping is necessary to use HAVING. Once we understand when each clause executes, we can combine WHERE and HAVING together: WHERE to eliminate individual rows and HAVING to filter out entire groups.

2.5 Demo: Fix common GROUP BY errors

PostgreSQL is strict on grouping rules, which is a good thing — it prevents ambiguous results.

The rule: each selected column must either be aggregated or appear in the GROUP BY.

Common error: column in SELECT not aggregated and missing from GROUP BY

-- INVALIDE : 'position' n'est pas dans GROUP BY et n'est pas agrégé.
SELECT
    department,
    position,
    COUNT(*) AS employee_count
FROM hr.hris
GROUP BY department;
-- ERREUR : column "hr.hris.position" must appear in the GROUP BY clause
--          or be used in an aggregate function

PostgreSQL cannot determine which position to display for each department — this is ambiguous.

Fix 1: Add column to GROUP BY

-- Corriger en ajoutant 'position' à la liste GROUP BY.
SELECT
    department,
    position,
    COUNT(*) AS employee_count
FROM hr.hris
GROUP BY
    department,
    position;

Fix 2: Aggregate the problematic column

-- Alternative : agréger 'position' au lieu de la sélectionner directement.
SELECT
    department,
    COUNT(DISTINCT position) AS position_count,
    COUNT(*)                  AS employee_count
FROM hr.hris
GROUP BY department;

Note: both corrections are valid but have different semantics and return different results. This is another reason to understand what is happening.

2.6 Demo: Present results with ORDER BY and LIMIT

Once the data is grouped and aggregates calculated, the next step is usually presentation. ORDER BY and LIMIT do not change how aggregates are calculated, but they significantly affect the ease of interpreting the results.

Sort by aggregate value

-- Résultats ordonnés par masse salariale totale (décroissant).
SELECT
    department,
    COUNT(*)      AS employee_count,
    SUM(salary)   AS total_payroll
FROM hr.hris
GROUP BY department
ORDER BY total_payroll DESC;

It is immediately clear which departments account for the largest share of payroll.

Limit to first N results

-- Afficher uniquement les 3 premiers départements par masse salariale.
SELECT
    department,
    COUNT(*)      AS employee_count,
    SUM(salary)   AS total_payroll
FROM hr.hris
GROUP BY department
ORDER BY total_payroll DESC
LIMIT 3;

LIMIT reduces the result set to the most significant groups, which is often what you want in a report or dashboard.

2.7 Over-clustering — how it distorts results

Grouping by additional columns can give more detail, but there is a drawback. Over-clustering can silently change the direction of results by breaking aggregates into smaller and smaller slices. These queries always execute and always seem reasonable — which is why it’s such a dangerous error.

Correct grouping (by department)

-- Regroupement utile : une ligne par département.
SELECT
    department,
    COUNT(*)    AS employee_count,
    SUM(salary) AS total_payroll
FROM hr.hris
GROUP BY department;

This result is easy to interpret: one line per department with clear metrics.

Over-bundling (excessive fragmentation)

-- Sur-regroupement : department + position + salary = chaque ligne est quasi-unique.
SELECT
    department,
    position,
    salary,
    COUNT(*) AS employee_count
FROM hr.hris
GROUP BY
    department,
    position,
    salary;

By grouping on department, position and salary, the data is so fragmented that most groups represent a single employee. The counts are technically correct, but more meaningful.

Classic real-world error: The query executes. The numbers seem plausible, but the level of grouping no longer corresponds to the business question. Each column added to GROUP BY increases the granularity. If we don’t think about the desired level of summary, we can end up with mathematically correct and analytically useless results.

Illustrative example in the BadQuery demo

-- Syntaxiquement valide mais analytiquement suspect.
SELECT
  department,
  employee_id,
  AVG(salary) AS avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department, employee_id
ORDER BY department;

-- Le sur-regroupement peut silencieusement effacer la valeur analytique.
SELECT
  department,
  hire_date,
  SUM(salary) AS dept_salary_total
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department, hire_date
ORDER BY department;

2.8 Data Reshaping by Clustering

GROUP BY fundamentally reshapes the data by reducing many rows to fewer rows. This trade-off—losing detail to gain summaries—is sometimes exactly what we want, and sometimes not. Understanding this limitation helps explain why more advanced techniques are needed.

Advanced GROUP BY options (not covered in detail in this course, but worth knowing):

These options are not needed every day, but they are available when needed.

3. Calculations with window functions

3.1 Demo: Row-level data vs grouped results

There are many analytical questions where you need an aggregate (an average or total), but you also need to see each individual row in the result. As soon as you enter GROUP BY, these lines disappear. This is not a syntax problem — it’s how grouping fundamentally works.

Example of question not possible with GROUP BY alone: “For each employee, display their salary and the average salary of their department. »

Step 1: retrieve employee details

-- Liste des employés actifs avec détails (une ligne par employé).
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, last_name, first_name;

Step 2: calculate the average by department (we lose employees)

-- Moyenne de salaire par département — mais les employés individuels disparaissent.
SELECT
  department,
  AVG(salary) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department
ORDER BY department;

Invalid attempt: keep employee columns in a GROUP BY

-- Tentative de garder employee_id dans un GROUP BY sans l'agréger → erreur.
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  AVG(salary) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department
ORDER BY department, last_name, first_name;
-- ERREUR : column "hr.hris.employee_id" must appear in the GROUP BY...

Fix with all fields in GROUP BY → over-grouping

-- Corriger l'erreur : ajouter toutes les colonnes au GROUP BY.
-- Résultat : une ligne par employé, mais AVG(salary) = salary de l'employé lui-même !
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  AVG(salary) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY
  employee_id,
  first_name,
  last_name,
  department
ORDER BY department, last_name, first_name;

We return to one line per employee, and the average is no longer the department average — it is the salary of the employee himself. The query answers a different question than the one asked.

Classic solution with self-join (before window functions)

-- Ancienne approche avec un JOIN sur une sous-requête agrégée.
SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  e.department,
  e.salary,
  d.dept_avg_salary
FROM hr.hris AS e
JOIN (
  SELECT
    department,
    AVG(salary) AS dept_avg_salary
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
) AS d
  ON d.department = e.department
WHERE e.termination_date IS NULL
ORDER BY e.department, e.last_name, e.first_name;

This approach works but is neither very readable nor very efficient.

3.2 Demo: Basic structure of a window function

Window functions take a different approach: they allow you to calculate values ​​on a set of linked rows, without reducing the result set. Each line remains visible.

The key syntax that makes this possible is the OVER clause. We can think of OVER as the statement that transforms a normal function into a window function. It tells the database how the rows are related to each other for the purpose of the calculation — this is called partitioning.

Technically, the OVER clause can be empty, but in practice it is not very useful. The important thing is that it does not reduce the lines.

Comparison: JOIN (old approach) vs window function

-- Ancienne approche avec JOIN (deux requêtes combinées).
SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  e.department,
  e.salary,
  d.dept_avg_salary
FROM hr.hris AS e
JOIN (
  SELECT
    department,
    AVG(salary) AS dept_avg_salary
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
) AS d
  ON d.department = e.department
WHERE e.termination_date IS NULL
ORDER BY e.department, e.last_name, e.first_name;

-- Même résultat avec une window function — une seule requête, plus simple.
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, last_name, first_name;

The only real change is the OVER clause — this is what tells PostgreSQL to calculate the average over related rows rather than reducing them into a grouped result. Each partition defines a set of rows that belong together for window calculation — the partition values ​​reset with each new partition value.

3.3 Demo: PARTITION BY vs GROUP BY

PARTITION BY looks a lot like GROUP BY, and that’s where people go wrong. Both define related rows, but only GROUP BY collapses the result set. PARTITION BY never does this — it only defines how the calculation runs.

Basic query (47 lines)

-- Employés actifs : 47 lignes.
SELECT
  employee_id,
  department,
  salary
FROM hr.hris
WHERE termination_date IS NULL;

With GROUP BY: reduction to 11 lines (one per department)

-- GROUP BY réduit le result set à 11 lignes (une par département).
SELECT
  department,
  AVG(salary) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department;

With window function: always 47 lines

-- Window function : 47 lignes conservées, moyenne de département ajoutée à chaque ligne.
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL;

GROUP BY reduces the result set. PARTITION BY does not do this. PARTITION BY defines how the calculation runs, not what the result set looks like.

3.4 Demo: Aggregate functions in windows

The same aggregation functions already known — SUM, AVG, etc. — also work as window functions. The key difference is that the calculation runs on a window, but the rows themselves remain intact.

AVG as window function

-- AVG comme window function : même résultat que GROUP BY mais lignes conservées.
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department;

Add multiple aggregates to the same partition

-- Ajouter un second agrégat : total des salaires par département.
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department;

Still 47 rows, but now each row has both the department’s average and total salaries.

Multi-column partition

-- Partition sur deux colonnes : département ET niveau de poste.
SELECT
  employee_id,
  department,
  position,
  salary,
  AVG(salary) OVER (
    PARTITION BY department, position
  ) AS dept_level_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, position;

When you add several columns to PARTITION BY, you refine the window. Each department + level combination gets its own average, but all employee lines remain.

3.5 Demo: Ranking functions

Ranking functions do not aggregate data — they assign a value to each row based on its position within a window. Sorting (ORDER BY) in the window is required for the ordering to make sense.

ROW_NUMBER() — unique number per row

-- Numéroter les employés dans chaque département par salaire décroissant.
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_row_number
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, salary DESC;

Note: the final ORDER BY in the query affects the entire result set after the ordering and ranking done in the window function.

RANK() — same rank for ties, with gaps

-- Utiliser RANK pour gérer les égalités de salaire (avec gaps).
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, salary DESC;

Equal salaries share the same rank, but after ties, the next rank is skipped (eg: 1, 2, 2, 4).

DENSE_RANK() — same rank for ties, without gaps

-- Utiliser DENSE_RANK pour des rangs continus sans gaps.
SELECT
  employee_id,
  department,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_dense_rank
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, salary DESC;

With DENSE_RANK, the ranks are continuous — there is no gap after ties (eg: 1, 2, 2, 3).

The function chosen depends on what the classification really means and what the business request expresses. There is no universally correct answer, it’s a matter of intention.

3.6 Demo: Window frames — window frame control

A window frame adds an extra layer beyond partitions and ordering. It controls what portion of the ordered window is used for each calculation. Most of the time the default frame is enough, but for running totals and progressive values ​​it’s important to be explicit.

Running total — default frame

-- Total cumulatif des salaires dans chaque département, ordonné par date d'embauche.
SELECT
  employee_id,
  department,
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) AS running_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, hire_date;

The default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) accumulates values ​​as the order progresses.

Explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Même calcul avec un frame ROWS explicite (traitement ligne par ligne strict).
SELECT
  employee_id,
  department,
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, hire_date;

Key difference between RANGE and ROWS: RANGE treats rows with the same order value as a group (the “peers”). ROWS processes rows one by one independently. When multiple rows share the same order value, the two frames may produce different results.

Moving average on a sliding window (3 lines)

-- Moyenne mobile des salaires (fenêtre de 3 lignes) par département.
SELECT
  employee_id,
  department,
  hire_date,
  salary,
  AVG(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department, hire_date;

For each line, include the 2 previous lines and the current line in the calculation.

WINDOW clause to factor complex window definitions

-- Utiliser la clause WINDOW pour réutiliser des définitions de fenêtres.
SELECT
  employee_id,
  department,
  hire_date,
  salary,
  COUNT(*) OVER w  AS cumulative_hires,
  AVG(salary) OVER w2 AS moving_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
WINDOW
  w  AS (PARTITION BY department ORDER BY hire_date),
  w2 AS (PARTITION BY department ORDER BY hire_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY department, hire_date;

Separation of concepts: RANGE and ROWS control which rows are included in the calculation itself. Functions like RANK and ROW_NUMBER do not change the calculation — they only change how rows are labeled.

3.7 Demo: Aggregation with and without partition — choosing the right tool

The key difference between GROUP BY and window functions is not syntax — it’s the form of the result set we need.

GROUP BY — summary results only

-- Une ligne par département avec la moyenne salariale.
SELECT
  department,
  AVG(salary) AS avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department
ORDER BY department;

Window function — detail + aggregate context on the same line

-- Une ligne par employé, avec la moyenne du département en contexte.
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM hr.hris
WHERE termination_date IS NULL
ORDER BY department;

Both queries are correct. The difference is not precision — it’s intent. GROUP BY reshapes data into summaries, while window functions add analytical context without collapsing rows. Getting this decision right matters more than memorizing the syntax.

PostgreSQL supports additional window functions for row-by-row comparisons, percentiles, and statistical analysis. These functions are based on the same concepts and deserve more targeted treatment in an advanced course.

4. Combining aggregation and filtering for analysis

4.1 Introduction: aggregations combined with JOINs

This final module brings everything together — aggregation, grouping, window functions, JOINs, and filtering — to build real analytics queries that stand up to scrutiny.

Key points covered:

• Stay at the correct level of detail (grain)
• Avoiding row multiplication pitfalls when JOINs come into play
• Handle missing values cleanly with COALESCE
• Validate results with quick consistency checks

Grain concept: Each aggregate has a grain — for example, one line per department or one line per employee. This grain is important because it defines what each line actually represents.

When attaching an aggregate to detail lines, this aggregate value is repeated once for each corresponding detail line. This repetition is expected behavior, but easy to overlook. The real problem appears when these repeated values ​​are aggregated again — the totals become inflated, and the query may run without errors while silently producing incorrect results.

4.2 Demo: Integrate aggregations and JOIN

Aggregation by department (correct)

-- Total des salaires par département pour les employés actifs.
SELECT
  department,
  SUM(salary) AS dept_salary_total
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department
ORDER BY dept_salary_total DESC;

JOIN aggregated totals to employee lines

-- Joindre les totaux département aux lignes employés via un CTE.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  h.employee_id,
  h.first_name,
  h.last_name,
  h.department,
  h.salary,
  dt.dept_salary_total
FROM hr.hris AS h
JOIN dept_totals AS dt
  ON dt.department = h.department
WHERE h.termination_date IS NULL
ORDER BY h.department, h.salary DESC;

This is a one-to-many JOIN: a department total corresponds to several employee lines. The department total is repeated on each employee row in that department — this is not a bug, it is normal JOIN behavior.

The double aggregation problem

-- Vérifier : que se passe-t-il si on agrège à nouveau après le JOIN ?
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
),
joined AS (
  SELECT
    h.employee_id,
    h.salary,
    dt.dept_salary_total
  FROM hr.hris AS h
  JOIN dept_totals AS dt
    ON dt.department = h.department
  WHERE h.termination_date IS NULL
)
SELECT
  SUM(salary) AS sum_of_employee_salaries,
  SUM(dept_salary_total) AS sum_of_repeated_department_totals
FROM joined;

The sum of repeated department totals will be much greater than the sum of individual salaries, because each department total was counted once per employee in that department.

Fundamental rule: Once you have joined an aggregate to detail lines, that aggregate is no longer safe to sum again — unless you deliberately deduplicate or re-aggregate in the right grain.

4.3 Demo: Pre-aggregation for correct results

The correct way to combine aggregation and JOIN is to aggregate correctly before the JOIN. By placing the aggregation in a subquery or CTE, we lock these totals in place so that they cannot be distorted by downstream JOINs.

Pre-aggregated CTE (fixed totals before JOIN)

-- Agréger dans un CTE avant tout JOIN à des données de détail.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  department,
  dept_salary_total
FROM dept_totals
ORDER BY dept_salary_total DESC;

Join fixed totals to employee lines

-- Joindre les totaux pré-agrégés aux lignes employés (répétition intentionnelle et sûre).
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  h.employee_id,
  h.first_name,
  h.last_name,
  h.department,
  h.salary,
  dt.dept_salary_total
FROM hr.hris AS h
JOIN dept_totals AS dt
  ON dt.department = h.department
WHERE h.termination_date IS NULL
ORDER BY h.department, h.salary DESC;

The department total appears on each line used, but now this repetition is intentional and safe because we are not going to sum it again.

Validation: the two totals must match

-- Vérifier que la pré-agrégation a préservé la correction.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  (SELECT SUM(salary)
   FROM hr.hris
   WHERE termination_date IS NULL) AS total_employee_salary,
  (SELECT SUM(dept_salary_total)
   FROM dept_totals) AS total_department_salary;

When performing this validation, the two totals must match. This is confirmation that pre-aggregation preserved the correction.

4.4 Demo: Calculate metrics with GROUP BY and aggregation

Once we have aggregated to the correct grain, we can calculate ratios, percentages and other derived values. The key is that both sides of the calculation — numerator and denominator — must come from compatible aggregates. If the grain is offset, the calculations can run, but the result will not mean what you think.

Totals by department (basis for metrics)

-- Totaux par département pour les employés actifs.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  department,
  dept_salary_total
FROM dept_totals
ORDER BY dept_salary_total DESC;

Calculation of percentage of total (basic version)

-- Calculer le pourcentage du total : chaque département représente quelle part ?
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
),
overall_total AS (
  SELECT
    SUM(dept_salary_total) AS total_salary
  FROM dept_totals
)
SELECT
  d.department,
  d.dept_salary_total,
  d.dept_salary_total / o.total_salary AS pct_of_total_salary
FROM dept_totals AS d
CROSS JOIN overall_total AS o
ORDER BY pct_of_total_salary DESC;

Formatting with ROUND and checking

-- Formatage du pourcentage avec ROUND et vérification de cohérence.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
),
overall_total AS (
  SELECT
    SUM(dept_salary_total) AS total_salary
  FROM dept_totals
)
SELECT
  department,
  dept_salary_total,
  ROUND(dept_salary_total / total_salary * 100, 2) AS pct_of_total_salary
FROM dept_totals
CROSS JOIN overall_total
ORDER BY pct_of_total_salary DESC;

Percentages should sum to ~100%, which is a quick consistency check that the calculation is correct.

4.5 Demo: Handle missing data with COALESCE

When an aggregate has no matching rows, Postgres returns NULL, not 0. This distinction is important, especially when these results feed into other calculations. The COALESCE function allows you to provide a safe default value so that calculations remain stable and predictable.

Problem: NULL in aggregates with LEFT JOIN

-- Construire la liste des départements, puis LEFT JOIN les employés terminés.
-- Les départements sans employés terminés affichent NULL.
WITH departments AS (
  SELECT DISTINCT department
  FROM hr.hris
),
dept_totals AS (
  SELECT
    d.department,
    SUM(h.salary) AS terminated_salary_total
  FROM departments AS d
  LEFT JOIN hr.hris AS h
    ON h.department = d.department
   AND h.termination_date IS NOT NULL
  GROUP BY d.department
)
SELECT *
FROM dept_totals
ORDER BY department;

The filter condition is placed in the JOIN clause (not in WHERE) — this detail is important. The LEFT JOIN preserves departments even when there are no matching employees under the filter. WHERE would have eliminated these departments.

Problem: NULLs propagate in calculations

-- Les NULL cassent les métriques dérivées.
WITH departments AS (
  SELECT DISTINCT department
  FROM hr.hris
),
dept_totals AS (
  SELECT
    d.department,
    SUM(h.salary) AS terminated_salary_total
  FROM departments AS d
  LEFT JOIN hr.hris AS h
    ON h.department = d.department
   AND h.termination_date IS NOT NULL
  GROUP BY d.department
)
SELECT
  department,
  terminated_salary_total,
  terminated_salary_total * 0.10 AS bonus_pool
FROM dept_totals
ORDER BY department;

Any department with a NULL total also gets a NULL bonus pool. The query runs successfully, but the result is unusable for these departments.

Solution: COALESCE to substitute 0 for NULLs

-- Utiliser COALESCE pour remplacer NULL par 0 dans les agrégats.
WITH departments AS (
  SELECT DISTINCT department
  FROM hr.hris
),
dept_totals AS (
  SELECT
    d.department,
    COALESCE(SUM(h.salary), 0) AS terminated_salary_total
  FROM departments AS d
  LEFT JOIN hr.hris AS h
    ON h.department = d.department
   AND h.termination_date IS NOT NULL
  GROUP BY d.department
)
SELECT
  department,
  terminated_salary_total,
  terminated_salary_total * 0.10 AS bonus_pool
FROM dept_totals
ORDER BY department;

Where to apply COALESCE: Used with care, it makes missing data explicit without hiding real problems. You should be specific about where you handle NULLs — in the aggregate or in the derived calculation.

4.6 Demo: Combine GROUP BY and window functions

You can actually combine grouping and window functions in the same query and get the benefits of both. GROUP BY remains the right tool to produce metrics at the category level (one line per department). Window functions complete this by adding global context (totals or percentages across all departments).

Used together, they allow both local and global information to be displayed without losing clarity or correctness. We get the strength of both.

GROUP BY alone

-- Totaux par département avec GROUP BY.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  department,
  dept_salary_total
FROM dept_totals
ORDER BY dept_salary_total DESC;

Add a grand total with a window function

-- Ajouter le total global via une window function sans PARTITION BY.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  department,
  dept_salary_total,
  SUM(dept_salary_total) OVER () AS overall_salary_total
FROM dept_totals
ORDER BY dept_salary_total DESC;

No PARTITION BY, so the window runs on all rows returned by the bulk query, giving the overall salary total next to each department row.

Percentage of total with GROUP BY + window function

-- Calcul du pourcentage du total via GROUP BY + window function.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  department,
  dept_salary_total,
  ROUND(
    dept_salary_total
    / SUM(dept_salary_total) OVER () * 100,
    2
  ) AS pct_of_total_salary
FROM dept_totals
ORDER BY pct_of_total_salary DESC;

Window functions allow ratios without additional JOINs or subqueries. Each row is the result of a GROUP BY, and the window function adds the context of the overall total.

4.7 Demo: Validate the correction of the results

The best query is useless unless you are sure that it returns the correct results. SQL will happily return a response even when the logic behind the aggregation is faulty. This means that correctness is not guaranteed just because a query runs.

Simple consistency checks — like comparing totals at different grains or validating counts — can quickly reveal problems before they reach a report or dashboard.

Establish a trust reference (baseline)

-- Total de référence : calculé directement depuis les lignes employés.
SELECT
  SUM(salary) AS total_salary
FROM hr.hris
WHERE termination_date IS NULL;

Verify that grouping does not change the overall total

-- Le total groupé par département doit réconcilier avec la référence.
SELECT
  department,
  SUM(salary) AS dept_salary_total
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department
ORDER BY department;

Confirm that totals agree between grains (with CTE)

-- Confirmer que la somme des totaux départementaux = total de référence.
WITH dept_totals AS (
  SELECT
    department,
    SUM(salary) AS dept_salary_total
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY department
)
SELECT
  SUM(dept_salary_total) AS summed_department_totals
FROM dept_totals;

Validation with ROLLUP

-- ROLLUP pour inclure le grand total automatiquement.
SELECT
  COALESCE(department, 'ALL') AS department,
  SUM(salary) AS dept_salary_total
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY ROLLUP (department)
ORDER BY (department = 'ALL'), department;

Detect row multiplication with a JOIN

-- Piège : un JOIN avec hr.employee_absence multiplie les lignes → total gonflé.
SELECT
  SUM(h.salary) AS total_salary
FROM hr.hris AS h
JOIN hr.employee_absence AS a
  ON a.employee_id = h.employee_id
WHERE h.termination_date IS NULL;

The total no longer corresponds to the reference. The query executes successfully, but the result is wrong because the JOIN multiplied the rows.

EXCEPT as validation tool

-- Utiliser EXCEPT pour détecter toute divergence entre deux calculs.
WITH baseline AS (
  SELECT
    'ALL'::text AS department,
    SUM(salary) AS total_salary
  FROM hr.hris
  WHERE termination_date IS NULL
),
rollup_total AS (
  SELECT
    'ALL'::text AS department,
    SUM(salary) AS total_salary
  FROM hr.hris
  WHERE termination_date IS NULL
  GROUP BY ROLLUP (department)
  HAVING department IS NULL
)
SELECT * FROM baseline
EXCEPT ALL
SELECT * FROM rollup_total;

If the query returns zero rows, both calculations are identical — validation successful.

Aggregate validation is not limited to checking totals. It’s also understanding what each line actually represents, and it’s this judgment that allows you to write trustworthy analysis queries.

4.8 Demo: Avoiding results without analytical meaning

SQL does not protect against an important category of problems: a query can be syntactically correct, return rows, and even appear plausible while still being analytically meaningless. Aggregates do not guarantee that a result is meaningful. This responsibility lies with the developer.

Syntactically valid but analytically suspect query

-- Un employé peut avoir plusieurs salaires différents ?
-- GROUP BY (department, employee_id) → AVG(salary) = salary de l'employé lui-même.
SELECT
  department,
  employee_id,
  AVG(salary) AS avg_salary
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department, employee_id
ORDER BY department;

Every department + employee combination has an “average salary”, but this should immediately raise a red flag.

Over-grouping that destroys the sense of the aggregate

-- Le total salarial du département est fragmenté par date d'embauche → perd tout sens.
SELECT
  department,
  hire_date,
  SUM(salary) AS dept_salary_total
FROM hr.hris
WHERE termination_date IS NULL
GROUP BY department, hire_date
ORDER BY department;

The label appears meaningful, but the result no longer represents a department-level total.

Questions to ask before validating a request:

1. Does each line represent something that can be explained in one sentence?
2. Does the aggregate align with the granularity of this row?
3. Would two different people interpret this result the same way?

If the answer to any of these questions is no, the query may work, but it doesn’t help. SQL will happily return results that are internally consistent but analytically empty. The database enforces syntax and types, not meaning — that’s why validating aggregates isn’t just about checking totals. It’s understanding what each line actually represents.

5. Appendix: Course Database

This course uses an HR analytics dataset (Human Resources). The main employee information is in the hr.hris table, and the absence data is in hr.employee_absence.

Restore script

# Restaurer une sauvegarde logique PostgreSQL (format custom) dans une base de données.
# -U <user>       : rôle PostgreSQL utilisé pour effectuer la restauration
# -d <database>   : base de données cible pour se connecter (pour les commandes CREATE/DROP)
# --clean         : supprimer les objets de base de données existants avant la restauration
# --create        : créer la base de données définie dans la sauvegarde
# backup.dump     : fichier de sauvegarde au format custom créé avec pg_dump -Fc

# Note : pg_restore demandera un mot de passe si requis.
# Les mots de passe ne sont pas passés en arguments de ligne de commande.

pg_restore -U <user> -d <database> --clean --create backup.dump

Sanity check

-- Vérifier la base de données courante.
SELECT current_database();

-- Lister toutes les tables du schéma hr.
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'hr'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Structure of main tables

Search Terms

group · aggregate · data · sql · postgresql · databases · window · aggregation · department · grouping · totals · conditional · function · join · lines · functions · total · null · query · without · aggregates · column · employee · filter