Query, summarise, join and change data in a relational database with SQL, practised in your browser.

SQL Fundamentals

## Why it matters

A manager rarely wants to see 40,000 order rows. They want "how many orders did we ship last month, what did they bring in, and what is a typical order worth?". Those are **aggregate** questions: many rows go in, one summary value comes out. Aggregates are the basis of every report, dashboard and KPI you will build.

## The five core aggregates

| Function | Returns |
|---|---|
| `COUNT(*)` | number of rows |
| `COUNT(column)` | number of rows where that column is not `NULL` |
| `SUM(column)` | total of the values |
| `AVG(column)` | mean (average) of the values |
| `MIN(column)` / `MAX(column)` | smallest / largest value |

Example `orders` table:

| id | customer | amount | status |
|---|---|---|---|
| 1 | Ana | 25.00 | shipped |
| 2 | Ben | 40.00 | shipped |
| 3 | Ana | 15.50 | cancelled |
| 4 | Chen | 60.00 | shipped |
| 5 | Dee | NULL | pending |

```sql
SELECT COUNT(*)    AS order_count,
       SUM(amount) AS total,
       AVG(amount) AS average,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM orders;
```

| order_count | total | average | smallest | largest |
|---|---|---|---|---|
| 5 | 140.5 | 35.125 | 15.5 | 60.0 |

The whole table collapses into **one row**.

## How NULLs affect aggregates

Look closely: `COUNT(*)` is 5, but the average is `140.5 / 4`, not `/ 5`. Every aggregate except `COUNT(*)` **ignores `NULL` values**. Order 5 has no amount yet, so it is skipped by `SUM`, `AVG`, `MIN`, `MAX` and `COUNT(amount)`.

That is usually what you want, but be deliberate. If a missing amount should count as zero, say so with `COALESCE(amount, 0)` (covered in this module's third lesson). The two choices give different averages, and a report should state which one it uses.

## Filtering before aggregating

`WHERE` runs **before** the aggregate, so it decides which rows are summarised:

```sql
SELECT COUNT(*) AS shipped_orders,
       SUM(amount) AS shipped_revenue
FROM orders
WHERE status = 'shipped';
```

| shipped_orders | shipped_revenue |
|---|---|
| 3 | 125.0 |

Cancelled and pending orders never reach `SUM`.

## Counting distinct values

`COUNT(DISTINCT column)` counts unique non-null values. "How many different customers placed an order?":

```sql
SELECT COUNT(DISTINCT customer) AS customers FROM orders;
```

Ana appears twice, so the answer is 4, not 5.

## Rounding the output

`AVG` often returns long decimals. Round for presentation:

```sql
SELECT ROUND(AVG(amount), 2) AS avg_order FROM orders WHERE status = 'shipped';
```

The shipped amounts are 25, 40 and 60, so the average is 41.666..., shown as `41.67`. Round in the final output only. If you round values before adding them up, small errors add up.

## Mixing aggregates with plain columns

This looks reasonable but is a mistake:

```sql
SELECT customer, SUM(amount) FROM orders;
```

You asked for one summary row (the `SUM`) and also a per-row value (`customer`). Which customer should appear? PostgreSQL, SQL Server and (by default) MySQL reject the query. SQLite runs it and picks a value from an arbitrary row, which silently gives a misleading answer. The fix is `GROUP BY`, covered in the next lesson: "one row **per customer**".

## Aggregates in expressions

Aggregates can be combined with arithmetic. Share of revenue from shipped orders:

```sql
SELECT ROUND(
         100.0 * SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)
               / SUM(amount), 1) AS pct_shipped
FROM orders;
```

You will meet `CASE` properly later in this module. Note the `100.0`: it keeps the division in decimals.

## An empty result still returns a row

If no rows match the `WHERE`, `COUNT(*)` returns `0` but `SUM`, `AVG`, `MIN` and `MAX` return `NULL`. A dashboard that shows "NULL" instead of "0" for a quiet day is a common bug; `COALESCE(SUM(amount), 0)` fixes it.

## Common mistakes

- **Expecting `COUNT(column)` to count every row.** It skips `NULL`s. Use `COUNT(*)` for rows.
- **Averaging with missing values without thinking.** Decide whether a `NULL` means "unknown" (skip it) or "zero" (`COALESCE`).
- **Putting an aggregate in `WHERE`.** `WHERE SUM(amount) > 100` is an error, because `WHERE` runs before aggregation. Group-level filters use `HAVING` (next lesson).
- **Selecting a plain column next to an aggregate** without `GROUP BY`.
- **Integer averages in some engines.** In PostgreSQL and SQL Server, `AVG` of an integer column can behave differently than you expect (SQL Server truncates to an integer). Cast to a decimal when precision matters.

## Quick recap

- `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` turn many rows into one value.
- All but `COUNT(*)` ignore `NULL`s.
- `WHERE` filters rows before they are aggregated.
- `COUNT(DISTINCT col)` counts unique values.
- Round only the final output.

## Try it

Open the Do tab and write a one-row summary of an online shop's shipped orders.


Summarise a whole table into one row with COUNT, SUM, AVG, MIN and MAX, and see how NULLs affect the results.

COUNT, SUM, AVG

Basic Aggregate Functions in SQL (COUNT, SUM, AVG, MAX, and MIN)

SQL Aggregate Functions | COUNT, SUM, AVG, MAX, MIN  | #SQL Course 21

## Why it matters

One total for the whole table is useful, but most questions are "per something": revenue **per month**, tickets **per team**, average score **per class**. `GROUP BY` splits the rows into groups and runs the aggregates once per group. `HAVING` then filters the groups. Together they turn a table into a report.

## GROUP BY: one row per group

Example `support_tickets` table:

| id | team | priority | hours_to_resolve |
|---|---|---|---|
| 1 | Billing | high | 4 |
| 2 | Billing | low | 10 |
| 3 | Network | high | 2 |
| 4 | Billing | high | 6 |
| 5 | Network | low | 8 |
| 6 | Accounts | low | 12 |

```sql
SELECT team,
       COUNT(*) AS tickets,
       AVG(hours_to_resolve) AS avg_hours
FROM support_tickets
GROUP BY team;
```

| team | tickets | avg_hours |
|---|---|---|
| Accounts | 1 | 12.0 |
| Billing | 3 | 6.666... |
| Network | 2 | 5.0 |

The database gathers rows with the same `team` into a bucket, then computes `COUNT` and `AVG` for each bucket. Each bucket becomes one output row.

## The golden rule

Every column in `SELECT` must either be **in the `GROUP BY`** or be **inside an aggregate**. `team` is in the `GROUP BY`; `COUNT(*)` and `AVG(...)` are aggregates. If you added `priority` to the `SELECT` without grouping by it, which priority should the Billing row show? It has both "high" and "low". Strict engines refuse; SQLite picks one arbitrarily, which is worse because it looks like it worked.

## Grouping by several columns

Group by more than one column to get a row per combination:

```sql
SELECT team, priority, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team, priority;
```

Billing now produces two rows, `(Billing, high, 2)` and `(Billing, low, 1)`.

You can also group by an expression, such as a month computed from a date: `GROUP BY strftime('%Y-%m', created_at)`.

## WHERE vs HAVING

Both filter, but at different times:

1. `FROM` picks the table.
2. `WHERE` removes **rows**, before grouping.
3. `GROUP BY` forms groups.
4. `HAVING` removes **groups**, after aggregating.
5. `SELECT` produces the columns, then `ORDER BY` sorts.

So `WHERE` cannot see aggregates (they do not exist yet), and `HAVING` is the place for conditions like "teams with at least 3 tickets":

```sql
SELECT team, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team
HAVING COUNT(*) >= 3;
```

Only Billing remains.

Use both in one query when you need to:

```sql
-- Among high-priority tickets only, teams averaging over 3 hours
SELECT team, AVG(hours_to_resolve) AS avg_hours
FROM support_tickets
WHERE priority = 'high'
GROUP BY team
HAVING AVG(hours_to_resolve) > 3;
```

`WHERE` throws away low-priority rows first; `HAVING` then keeps teams whose high-priority average is over 3. Billing (4 and 6, average 5) stays; Network (2) goes.

A good habit: if a condition does not involve an aggregate, put it in `WHERE`. It is clearer and lets the database discard rows early.

## Sorting grouped results

Groups come back in no guaranteed order. Add `ORDER BY`, often on an aggregate:

```sql
SELECT team, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team
ORDER BY tickets DESC;
```

Most engines let you sort by the alias (`tickets`), because `ORDER BY` runs after `SELECT`. Some engines, including SQLite and MySQL, also accept the alias in `HAVING`, but PostgreSQL and SQL Server do not, so repeating the aggregate in `HAVING` is the portable choice.

## Worked example

A help-desk lead asks: "For each team with at least two tickets, how many tickets and what is the average resolution time, rounded to one decimal, slowest team first?"

```sql
SELECT team,
       COUNT(*) AS tickets,
       ROUND(AVG(hours_to_resolve), 1) AS avg_hours
FROM support_tickets
GROUP BY team
HAVING COUNT(*) >= 2
ORDER BY avg_hours DESC;
```

| team | tickets | avg_hours |
|---|---|---|
| Billing | 3 | 6.7 |
| Network | 2 | 5.0 |

Reading it in processing order makes it easy to check: all tickets, grouped by team, groups with two or more kept, columns computed, sorted.

## Common mistakes

- **Selecting a column that is neither grouped nor aggregated.**
- **Putting aggregate conditions in `WHERE`.** `WHERE COUNT(*) > 2` is an error; use `HAVING`.
- **Putting row conditions in `HAVING`.** It may work, but it filters late and confuses readers.
- **Forgetting that `NULL` forms its own group.** Rows with a missing `team` are grouped together under `NULL`.
- **Expecting sorted groups without `ORDER BY`.**

## Quick recap

- `GROUP BY` produces one output row per distinct value (or combination).
- Every selected column is grouped or aggregated.
- `WHERE` filters rows before grouping; `HAVING` filters groups after.
- Add `ORDER BY` to control the order of groups.

## Try it

Open the Do tab and build a per-team help-desk report that only includes busy teams.


Aggregate rows per group with GROUP BY, and keep only the groups you want with HAVING.

GROUP BY and HAVING

Advanced Aggregate Functions in SQL (GROUP BY, HAVING vs. WHERE)

SQL Group By: An Explanation and How To Use It

## Why it matters

Real tables have gaps: a customer who did not give a phone number, an employee not yet assigned to a department, a measurement that failed. SQL marks these with `NULL`. Handling `NULL` correctly is one of the main differences between a query that looks right and one that is right. In the same lesson you will learn `CASE`, which lets you turn raw values (including missing ones) into readable categories.

## What NULL means

`NULL` means "unknown" or "no value". It is not zero and not an empty string. `''` is a known, empty piece of text; `NULL` is the absence of any value.

Because `NULL` is unknown, comparing anything with it gives **unknown**, not true or false:

```sql
SELECT 5 = NULL;     -- NULL
SELECT NULL = NULL;  -- NULL (two unknowns are not known to be equal)
```

`WHERE` keeps only rows where the condition is **true**, so `WHERE phone = NULL` keeps nothing, ever. Use the special operators instead:

```sql
SELECT name FROM employees WHERE phone IS NULL;      -- missing phone
SELECT name FROM employees WHERE phone IS NOT NULL;  -- has a phone
```

## NULL in arithmetic and text

Any arithmetic with `NULL` gives `NULL`: `salary + NULL` is `NULL`. Joining text with `||` also gives `NULL` if any part is `NULL`, so `first_name || ' ' || middle_name` disappears for anyone without a middle name.

## A trap with NOT and <>

Suppose some employees have no department. This query does **not** return them:

```sql
SELECT name FROM employees WHERE department <> 'Sales';
```

For a `NULL` department, `NULL <> 'Sales'` is unknown, so the row is dropped. If you mean "everyone not in Sales, including the unassigned", say so:

```sql
WHERE department <> 'Sales' OR department IS NULL
```

## COALESCE: supply a fallback

`COALESCE(a, b, c, ...)` returns the first argument that is not `NULL`. It is standard SQL and works in every major engine.

```sql
SELECT name,
       COALESCE(department, 'Unassigned') AS department,
       COALESCE(mobile, office_phone, 'no phone') AS contact
FROM employees;
```

SQLite and MySQL also have `IFNULL(a, b)`, and SQL Server has `ISNULL(a, b)`; these take exactly two arguments. `NULLIF(a, b)` does the reverse: it returns `NULL` when `a = b`. It is handy to avoid division by zero: `total / NULLIF(count, 0)` returns `NULL` instead of an error or a wrong result.

## CASE: if-then logic in a query

`CASE` evaluates conditions in order and returns the value for the first one that is true:

```sql
SELECT name,
       salary,
       CASE
         WHEN salary IS NULL  THEN 'unknown'
         WHEN salary < 50000  THEN 'entry'
         WHEN salary < 80000  THEN 'mid'
         ELSE 'senior'
       END AS salary_band
FROM employees;
```

Points to notice:

- Conditions are checked **top to bottom** and the first match wins. A salary of 45,000 matches `< 50000` and stops there, so the second test does not need `salary >= 50000`.
- Without `ELSE`, anything that matches no branch becomes `NULL`.
- `NULL` matches no comparison, so check `IS NULL` first if missing values need their own label. If the `IS NULL` branch were missing here, a `NULL` salary would fall through to `ELSE` and be labelled "senior".

There is also a shorter form for comparing one value with constants:

```sql
CASE status
  WHEN 'A' THEN 'Active'
  WHEN 'S' THEN 'Suspended'
  ELSE 'Other'
END
```

## CASE with aggregates

`CASE` inside `SUM` or `COUNT` builds a small pivot table in one pass:

```sql
SELECT department,
       COUNT(*) AS staff,
       SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS missing_phone
FROM employees
GROUP BY department;
```

This is a standard data-quality check: how many records per group are missing a key field?

## Worked example

HR wants a directory showing every employee's department (or "Unassigned") and a salary band, with unknown salaries labelled clearly:

```sql
SELECT name,
       COALESCE(department, 'Unassigned') AS department,
       CASE
         WHEN salary IS NULL THEN 'unknown'
         WHEN salary < 50000 THEN 'entry'
         WHEN salary < 80000 THEN 'mid'
         ELSE 'senior'
       END AS salary_band
FROM employees;
```

Nothing in the table changes; you are labelling the output.

## Common mistakes

- **`= NULL` or `<> NULL`.** Always `IS NULL` / `IS NOT NULL`.
- **Forgetting that `<>` and `NOT IN` skip `NULL` rows.**
- **Treating `''` and `NULL` as the same.** Check for both if your data has both: `WHERE phone IS NULL OR phone = ''`.
- **CASE branches in the wrong order.** Put the most specific tests (including `IS NULL`) first.
- **Missing `ELSE`.** Unmatched rows silently become `NULL`.

## Quick recap

- `NULL` means unknown; comparisons with it are unknown, so use `IS NULL`.
- Arithmetic and `||` with `NULL` give `NULL`.
- `COALESCE` returns the first non-null value; `NULLIF` turns a value into `NULL`.
- `CASE WHEN ... THEN ... ELSE ... END` buckets values; the first true branch wins.

## Try it

Open the Do tab and build an HR directory that handles missing departments and salaries.


Handle missing values with IS NULL and COALESCE, and turn raw values into categories with CASE.

NULLs and CASE

SQL - NULL Values - W3Schools.com

Writing CASE WHEN Statements in SQL (IF/THEN)

Summarise tables with COUNT, SUM and AVG, aggregate by group with GROUP BY and HAVING, and handle missing values with NULL checks and CASE.