In Lesson 21.1 you learned to read a query as a precise question: pick columns, pick a table, filter rows, sort, and limit. That handles "show me these reports." But most product screens do not show raw rows — they show summaries. "How many reports per sector?" "What is the average rating by analyst?" "Which support queue is overloaded?" Those are counting and grouping questions, and they are the everyday work of SQL.

Start from the question, not the keyword

Every new SQL keyword in this lesson answers a question you already know how to ask in English. Learn the question first; the keyword is just its name.

Using Sonariq's reports: "March energy reports, newest first" is a filter plus a sort. "How many reports per sector?" is a grouped count. Same table, different questions, different answer shapes.

Filter and sort: the product list

Filtering and sorting produce a list: one row per matching record, in a chosen order.

SELECT company_name, report_date, sector, rating
FROM research_reports
WHERE sector = 'Energy'
  AND report_date >= '2026-03-01'
  AND report_date < '2026-04-01'
ORDER BY report_date DESC
LIMIT 20;

In English: "March 2026 energy reports, newest first, twenty at most." Combine conditions with AND or OR, with parentheses when mixing them. Sorting can use several keys: ORDER BY sector ASC, report_date DESC groups sectors alphabetically, newest first inside each.

Count, average, total: the summary

Aggregation collapses many rows into one number.

SELECT COUNT(*) AS report_count,
       AVG(rating_score) AS avg_score,
       SUM(page_views) AS total_views
FROM research_reports
WHERE report_date >= '2026-01-01';

This returns one row with three numbers. COUNT(*) counts rows; COUNT(rating_score) counts only non-NULL values — useful when some reports are unrated. AVG and SUM ignore NULLs rather than treating them as zero.

The key shift: a filter-and-sort query answers "which rows?" while an aggregate answers "what does the whole set add up to?" One returns a list; the other returns a single summary row.

GROUP BY: one summary row per category

GROUP BY sits between those two shapes: it splits rows into buckets, then summarizes each bucket.

SELECT sector, COUNT(*) AS report_count
FROM research_reports
WHERE report_date >= '2026-01-01'
GROUP BY sector
ORDER BY report_count DESC;

In English: "For 2026 reports, count reports per sector, biggest sector first." The answer has one row per sector with its count — not one row per report. Add more summaries per bucket freely:

SELECT sector,
       COUNT(*) AS report_count,
       AVG(rating_score) AS avg_score
FROM research_reports
WHERE report_date >= '2026-01-01'
GROUP BY sector
HAVING COUNT(*) >= 5
ORDER BY avg_score DESC;

HAVING is the group-level twin of WHERE: WHERE filters rows *before* grouping, HAVING filters groups *after* grouping. Here it keeps only sectors with at least five reports. A reliable memory aid: if the condition mentions an aggregate like COUNT(*), it belongs in HAVING; if it mentions a plain row column like sector, it belongs in WHERE.

The classic beginner error: mixing rows and summaries

This query looks reasonable and fails:

-- WRONG: mixing a row column with a summary
SELECT company_name, COUNT(*)
FROM research_reports;

company_name wants one row per report; COUNT(*) wants one number for the whole table. The database cannot return both shapes at once, so Postgres rejects it (some databases return a misleading answer, which is worse). The rule: every column in the SELECT must either appear in the GROUP BY or sit inside an aggregate function. Decide first: do I want a list of rows, or a summary of groups? Then write the query for exactly one of them.

-- List: one row per report
SELECT company_name, sector FROM research_reports LIMIT 20;

-- Summary: one row per sector
SELECT sector, COUNT(*) FROM research_reports GROUP BY sector;

Two intentions, two queries, no mixing.

Beyond finance: support tickets by status

These patterns are not financial. Imagine a support app with a tickets table (ticket_id, status, category, created_at):

SELECT status, COUNT(*) AS open_by_status
FROM tickets
WHERE status IN ('open', 'pending', 'escalated')
GROUP BY status
ORDER BY open_by_status DESC;

"How many unresolved tickets per status, worst queue first?" Same grammar as the sector count — only the nouns changed.

Verify against a small known set

Grouped results are easy to trust blindly and easy to verify: keep a tiny dataset where you already know the answer.

Use this printed ten-row table as your known set — no inventing data. Copy it onto paper or into a scratch file exactly as shown:

reports (known set — 10 rows)
#  | company     | sector     | rating_score
---+-------------+------------+-------------
1  | Acme Corp   | Energy     | 4
2  | Delta Co    | Energy     | 3
3  | Echo Ltd    | Energy     | 5
4  | Foxtrot Inc | Energy     | 4
5  | Beta Inc    | Healthcare | 5
6  | Gamma LLC   | Healthcare | 4
7  | Helix Co    | Healthcare | NULL (not yet rated)
8  | Iota Corp   | Technology | 3
9  | Kappa Ltd   | Technology | 2
10 | Lambda Inc  | Technology | 4

Sector totals: Energy 4, Healthcare 3, Technology 3. One NULL rating (Helix Co, row 7) — aggregates ignore it rather than treating it as zero.

Expected answers for your three hand-checks:

1. Filter — all Healthcare rows.

SELECT company, sector FROM reports
WHERE sector = 'Healthcare'
LIMIT 20;

Expected: 3 rows — Beta Inc, Gamma LLC, Helix Co. If you get 2, a filter dropped the NULL row or the text did not match exactly.

2. Sorted — all rows by score, highest first.

SELECT company, sector, rating_score FROM reports
ORDER BY rating_score DESC
LIMIT 20;

Expected top-to-bottom: Beta Inc 5, Echo Ltd 5, Acme Corp 4, Foxtrot Inc 4, Gamma LLC 4, Lambda Inc 4, Delta Co 3, Iota Corp 3, Kappa Ltd 2, Helix Co NULL last. Ties may appear in any order among equal scores — that is normal without a second sort key.

3. Grouped count + average per sector.

SELECT sector, COUNT(*) AS report_count,
       AVG(rating_score) AS avg_score
FROM reports
GROUP BY sector
ORDER BY sector;

Expected:

sector     | report_count | avg_score | why
-----------+--------------+-----------+--------------------------
Energy     | 4            | 4.0       | (4+3+5+4)/4
Healthcare | 3            | 4.5       | (5+4)/2 — NULL ignored
Technology | 3            | 3.0       | (3+2+4)/3

Check: counts sum to 10 (4+3+3). COUNT(rating_score) would instead return 4, 2, 3 (9 total) because it skips the NULL — use that difference to confirm you understand NULL handling. If any grouped count disagrees with your hand count, a filter admitted a wrong row or a join duplicated one (Lesson 21.3). Run the plain filtered list first, count by eye, then compare against the grouped query.

Visual: one dataset, three views

FILTERED LIST              SORTED LIST                 GROUPED SUMMARY
WHERE sector='Energy'      ORDER BY report_date DESC   GROUP BY sector

Acme  | 2026-08-14         Acme  | 2026-08-14          Energy     | 4
Delta | 2026-05-30         Beta  | 2026-07-02          Healthcare | 3
...                        Delta | 2026-05-30          Technology | 3

One row per match.         One row per match,          One row per GROUP.
                           newest first.              COUNT(*) per bucket.

Same underlying rows; three different questions; three different answer shapes. Before writing SQL, name which of the three you need.

Practical exercise: ten rows, hand-verified

1. Copy the printed ten-row reports table above onto paper or into a scratch file — do not invent your own data for this first pass. 2. Write one filter ("all Healthcare rows"), one sorted result ("all rows by score, highest first"), and one grouped count ("count per sector"). Draft them yourself or with AI, keeping a LIMIT on the lists. 3. Verify by hand: count the paper rows per sector and confirm the grouped query's numbers match exactly. Confirm the sorted result's order by reading the scores top to bottom.

Finish line: the ten-row table plus three queries with their answers, each marked verified-by-hand with totals that reconcile (group counts sum to 10).

Verify: hide the queries, recount the paper table, and re-derive the expected counts. If the numbers disagree, check WHERE vs HAVING placement and NULL handling first.

Common failure mode: a grouped count that silently drops a category because a WHERE filtered its rows out before grouping. When a category is missing, inspect the filter, not the GROUP BY.

Check your understanding

1. What is the difference between "show every report" and "show the number of reports per sector" — in answer shape, not just keywords? 2. When does a condition belong in WHERE versus HAVING? 3. Why is SELECT company_name, COUNT(*) FROM research_reports; an error? 4. How do you verify a grouped result against a small known set?