Basic SQL 2

October 21 · Summarize groups without hiding the denominator

MaDS Databases & SQL

One question will organize today

Which states have the strongest station coverage—and what exactly are we counting?

Today, one table becomes a defensible summary.

Course source and adaptation

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s concepts follow the grouping and aggregation material in his SQL Basics chapter; 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.

SELECT station_id, state, elevation
FROM stations
LIMIT 5;

Before summarizing, say what one input row means.

Aggregates collapse rows

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

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

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

Return the number of station rows, the number with known elevation, and the number missing elevation.

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

Checkpoint 1: one solution

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

The two counts differ because aggregate functions generally ignore NULL, while COUNT(*) counts rows.

GROUP BY changes the output grain

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:

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

This does not:

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

Which single station name should represent an entire state?

Aggregates can be sorted by alias

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

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

Start with:

SELECT state,
FROM stations
WHERE
GROUP BY
ORDER BY

Checkpoint 2: one solution

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

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

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

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

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

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

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

AVG(elevation)

means:

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

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

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.