---
title: "Basic SQL 2"
subtitle: "October 21 · Summarize groups without hiding the denominator"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
Which states have the strongest station coverage—and what exactly are we counting?
:::

Today, one table becomes a defensible summary.

## Course source and adaptation

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's concepts follow the grouping and aggregation material in his [SQL Basics chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/sql.html); examples and checkpoints use the Fall 2026 weather database.
:::

## By the end of class

You should be able to:

- summarize rows with aggregate functions,
- explain the difference between `COUNT(*)` and `COUNT(column)`,
- create one result row per group,
- distinguish `WHERE` from `HAVING`,
- state the numerator, denominator, and exclusions behind a percentage.

## Begin with the grain

In `stations`, one row represents **one weather station**.

```sql
SELECT station_id, state, elevation
FROM stations
LIMIT 5;
```

Before summarizing, say what one input row means.

## Aggregates collapse rows

```sql
SELECT
  COUNT(*) AS station_count,
  MIN(elevation) AS min_elevation_m,
  MAX(elevation) AS max_elevation_m,
  AVG(elevation) AS mean_elevation_m
FROM stations;
```

Many station rows become one summary row.

## Five aggregates cover a lot of work

| Function | Question answered |
|---|---|
| `COUNT(...)` | How many? |
| `SUM(...)` | What total? |
| `AVG(...)` | What mean? |
| `MIN(...)` | What smallest value? |
| `MAX(...)` | What largest value? |

The function must match the business question.

## `COUNT(*)` counts rows

```sql
SELECT COUNT(*) AS station_rows
FROM stations;
```

This includes rows whose `elevation` is `NULL`.

It answers: “How many station records exist?”

## `COUNT(column)` counts known values

```sql
SELECT
  COUNT(*) AS station_rows,
  COUNT(elevation) AS known_elevations
FROM stations;
```

`COUNT(elevation)` ignores rows where elevation is `NULL`.

The difference measures missingness.

## Checkpoint 1: name the denominator

::: {.checkpoint}
Return the number of station rows, the number with known elevation, and the number missing elevation.
:::

```sql
SELECT
  COUNT(*) AS station_rows,
  COUNT(elevation) AS known_elevations,
  _____ AS missing_elevations
FROM stations;
```

## Checkpoint 1: one solution

```sql
SELECT
  COUNT(*) AS station_rows,
  COUNT(elevation) AS known_elevations,
  COUNT(*) - COUNT(elevation) AS missing_elevations
FROM stations;
```

::: {.answer}
The two counts differ because aggregate functions generally ignore `NULL`, while `COUNT(*)` counts rows.
:::

## `GROUP BY` changes the output grain

```sql
SELECT
  state,
  COUNT(*) AS station_count
FROM stations
GROUP BY state;
```

The result now has one row per state.

## Selected columns must make sense per group

This works:

```sql
SELECT state, COUNT(*)
FROM stations
GROUP BY state;
```

This does not:

```sql
SELECT state, name, COUNT(*)
FROM stations
GROUP BY state;
```

Which single station name should represent an entire state?

## Aggregates can be sorted by alias

```sql
SELECT
  state,
  COUNT(*) AS station_count
FROM stations
WHERE state IS NOT NULL
GROUP BY state
ORDER BY station_count DESC, state;
```

Sorting turns a summary into a ranking.

## Checkpoint 2: summarize states

::: {.checkpoint}
For every known state, return its station count and mean known elevation. Show the states with the most stations first.
:::

Start with:

```sql
SELECT state,
FROM stations
WHERE
GROUP BY
ORDER BY
```

## Checkpoint 2: one solution

```sql
SELECT
  state,
  COUNT(*) AS station_count,
  ROUND(AVG(elevation)::numeric, 1) AS mean_elevation_m
FROM stations
WHERE state IS NOT NULL
GROUP BY state
ORDER BY station_count DESC, state;
```

`AVG(elevation)` excludes missing elevations automatically.

## `WHERE` filters rows before grouping

```sql
SELECT state, COUNT(*) AS high_station_count
FROM stations
WHERE elevation >= 500
GROUP BY state;
```

This counts only stations surviving the row-level condition.

## `HAVING` filters completed groups

```sql
SELECT state, COUNT(*) AS station_count
FROM stations
WHERE state IS NOT NULL
GROUP BY state
HAVING COUNT(*) >= 20;
```

`HAVING` asks whether the completed group qualifies.

## Read the logical pipeline

```text
FROM       choose source rows
WHERE      remove individual rows
GROUP BY   form groups
HAVING     remove completed groups
SELECT     calculate displayed values
ORDER BY   arrange the result
LIMIT      keep a requested number
```

This is a reasoning order, not the written clause order.

## Percentages expose the denominator

```sql
SELECT
  state,
  COUNT(*) AS station_count,
  ROUND(100.0 * COUNT(elevation) / COUNT(*), 1) AS pct_elevation_known
FROM stations
WHERE state IS NOT NULL
GROUP BY state;
```

`100.0` keeps the division from becoming integer arithmetic.

## Checkpoint 3: find weak coverage

::: {.checkpoint}
Among states with at least 20 station records, find the lowest percentage with known elevation.
:::

Return the state, total station count, known-elevation count, and percentage. Sort lowest percentage first.

## Checkpoint 3: one solution

```sql
SELECT
  state,
  COUNT(*) AS station_count,
  COUNT(elevation) AS known_elevations,
  ROUND(100.0 * COUNT(elevation) / COUNT(*), 1) AS pct_known
FROM stations
WHERE state IS NOT NULL
GROUP BY state
HAVING COUNT(*) >= 20
ORDER BY pct_known, state;
```

## A mean also has a denominator

```sql
AVG(elevation)
```

means:

```text
sum of known elevations
───────────────────────
count of known elevations
```

It does **not** use all station rows when elevation is missing.

## Correct SQL does not guarantee an honest claim

“State A has better coverage” may mislead because:

- station count ignores land area,
- active dates may differ,
- missingness may be systematic,
- observation counts are not station counts.

## Project transfer

::: {.project-prompt}
Choose one project table and produce a group-level quality summary.
:::

Include:

- a stated input and output grain,
- one count of all rows and one count of known values,
- a `HAVING` condition justified in plain language.

## Homework starts here

Save today's project-transfer query as the first draft of Assignment 1.

Add comments answering:

1. What does one input row represent?
2. What does one output row represent?
3. What is the denominator?
4. Which records are excluded?

## The pattern to keep

```text
define the grain
  → filter rows
  → form groups
  → calculate summaries
  → filter groups
  → inspect the denominator
  → make a defensible claim
```

Next: connect station facts to observation records with joins.