Counting Rows with COUNT(*) and WHERE

Counting Rows with COUNT(*) 🔢

Scene: your first data-reconciliation task. The head of the desk forwards you a nightly price feed and one terse line: "Ops says the AAPL history looks short. Confirm we have a full two years before the risk model runs at 6am." You do not need the prices themselves yet - you need a headcount. Open the wrong table, count the wrong ticker, and the risk model silently runs on a gap. The very first query a professional writes against any new table is not `SELECT *` - it is "how many rows are in here, and how many match what I care about?"

🧠 The mental model: a bouncer with a clicker

Picture a bouncer at the door of a club holding a hand clicker. Every person who walks through, click - the count goes up by one. That is exactly what `COUNT(*)` does: it walks the rows one by one and clicks.

Now add a velvet rope. The bouncer only clicks people on the guest list - everyone else is waved past uncounted. That velvet rope is the `WHERE` clause. `COUNT(*)` is the clicker; `WHERE` decides who gets clicked.

COUNT(*) - click every row

```sql SELECT COUNT(*) FROM equity_prices; ```

This counts every row across all tickers - AAPL, MSFT, JPM, GS and SPY combined. Useful, but rarely the number you actually want.

WHERE - hang the velvet rope

`WHERE` filters before the clicker counts, so only rows that pass the test are tallied:

```sql SELECT COUNT(*) FROM equity_prices WHERE ticker = 'AAPL'; ```

The whole table vs. one ticker

Our `equity_prices` table holds five tickers, each with roughly two years of daily bars. So the numbers are on completely different scales:

If your "AAPL count" comes back in the thousands, you forgot the velvet rope - you counted the whole club, not the guest list.

Combining conditions

```sql -- AND: both conditions must be true SELECT COUNT(*) FROM equity_prices WHERE ticker = 'AAPL' AND close_price > 170;

-- OR: at least one condition must be true SELECT COUNT(*) FROM equity_prices WHERE ticker = 'AAPL' OR ticker = 'MSFT'; ```

💡 Text values use single quotes in SQL: `'AAPL'`, never `"AAPL"`. Double quotes mean something different (an identifier), and mixing them up is the #1 beginner error.

Your Task

The starter already counts rows - but it counts the entire table, every ticker at once. Your job is to hang the velvet rope: add a `WHERE` clause so it counts only the AAPL rows.

Predict before you run: the unfiltered count covers five tickers of similar length. If AAPL alone is in the hundreds, roughly what magnitude should the all-tickers total be? Run the starter first to see the big number, then add your filter and watch it shrink to the AAPL-only figure. That gap is the whole point of `WHERE`.