Aggregates

Dashboard cards, pipeline totals, "how many contacts per city" - that's aggregate territory.

The usual suspects

Function	Job
COUNT(*)	how many rows
COUNT(col)	how many non-NULL values in col
SUM(col)	add numbers
AVG(col)	average
MIN(col) / MAX(col)	extremes

SELECT COUNT(*) AS total_contacts
FROM contacts;

SELECT
  COUNT(*) AS total,
  COUNT(city) AS with_city,
  COUNT(*) - COUNT(city) AS missing_city
FROM contacts;

As you can see - COUNT(city) skips NULLs. COUNT(*) counts rows.

GROUP BY ≈ groupBy in JS

SELECT city, COUNT(*) AS n
FROM contacts
GROUP BY city
ORDER BY n DESC;

FE sketch:

Object.groupBy(contacts, c => c.city)
// then count each bucket

Rules of thumb:

• Every non-aggregated column in SELECT should appear in GROUP BY
• You're answering "per city / per stage / per owner…" questions

HAVING ≈ filter after grouping

WHERE filters rows before grouping. HAVING filters groups after.

SELECT city, COUNT(*) AS n
FROM contacts
WHERE city IS NOT NULL
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY n DESC;

Comments for the code above:

• WHERE city IS NOT NULL - drop empty cities first
• HAVING COUNT(*) >= 2 - only keep cities with at least 2 contacts
• You can't put COUNT(*) in WHERE - that's what HAVING is for

CRM-shaped examples (preview)

-- deals per stage (pipeline columns)
SELECT stage, COUNT(*) AS deals, SUM(amount) AS total_amount
FROM deals
WHERE deleted_at IS NULL
GROUP BY stage;

-- contacts per company
SELECT company_id, COUNT(*) AS contacts
FROM contacts
GROUP BY company_id;

We'll build the real tables soon. The pattern is already here: group → count/sum → sort → show on a board.

NULL groups

Rows with city = NULL form their own group. Sometimes you want that ("Unknown" bucket), sometimes you filter them out with WHERE first. Product call.

Next up: inventing the shapes ourselves - CREATE TABLE.