Intermediate SQL: GROUP BY, HAVING and JOINs

In the last lesson you filtered and sorted individual rows. Real questions are usually about groups: revenue per region, orders per channel, profit per category. That's what GROUP BY does. And because the information you need is often split across tables (sales in one, product costs in another), you also need JOINs. These two tools cover the majority of everyday analyst SQL and most SQL interview questions. Every result below comes from the practice tables.

Aggregate functions

An aggregate function turns many rows into one value:

SELECT COUNT(*)          AS orders,
       SUM(revenue)      AS revenue,
       ROUND(AVG(revenue), 2) AS avg_order,
       MIN(revenue)      AS smallest,
       MAX(revenue)      AS largest
FROM sales;

Same numbers as the spreadsheet in Lesson 6: a good sign your data loaded correctly. COUNT(*) counts rows; COUNT(column) counts only non-NULL values in that column, a difference that matters with LEFT JOINs later in this lesson.

GROUP BY: one row per group

Add GROUP BY and the aggregates are calculated separately for each group:

SELECT region,
       COUNT(*)               AS orders,
       SUM(revenue)           AS revenue,
       ROUND(AVG(revenue), 2) AS avg_order
FROM sales
GROUP BY region
ORDER BY revenue DESC;

This is the SQL version of a pivot table with region in Rows. Group by two columns (GROUP BY region, channel) and you get one row per combination: 4 regions × 3 channels = 12 rows, such as West / Wholesale = 28,131 and South / Retail = 4,497.

WHERE vs. HAVING

Both filter, but at different moments:

• WHERE filters rows before grouping. It can't use aggregates.
• HAVING filters groups after grouping. It's where conditions like SUM(revenue) > 10000 go.

Products that brought in more than $10,000:

SELECT product, SUM(units) AS units, SUM(revenue) AS revenue
FROM sales
GROUP BY product
HAVING SUM(revenue) > 10000
ORDER BY revenue DESC;

You can use both in one query. "In the West, revenue by channel":

SELECT channel, COUNT(*) AS orders, SUM(revenue) AS revenue
FROM sales
WHERE region = 'West'          -- row filter, before grouping
GROUP BY channel
ORDER BY revenue DESC;

Fourteen wholesale orders out-earn the other 50 West orders combined ($8,495) by more than three to one.

The order SQL runs a query

You write SELECT first, but the database runs the clauses in a different order. Knowing it explains most error messages:

1. FROM / JOINGet the rowsLoad the table(s) and combine them.
2. WHEREFilter rowsDrop rows that don't match. Aliases from SELECT don't exist yet.
3. GROUP BYForm groupsBundle rows that share the same values.
4. HAVINGFilter groupsKeep groups whose aggregates meet the condition.
5. SELECTCalculate columnsCompute expressions and aggregates, assign aliases.
6. ORDER BY / LIMITSort and trimAliases can be used here, because SELECT already ran.

That's why WHERE avg_order > 450 fails (the alias doesn't exist when WHERE runs) while ORDER BY revenue DESC using an alias works fine.

JOINs: combining tables

The sales table doesn't know what each product costs. The products table does. A JOIN matches rows from both tables using a common column, here product.

INNER JOIN: gross profit by category

SELECT s.category,
       SUM(s.revenue)                         AS revenue,
       SUM(s.units * p.unit_cost)             AS cost,
       SUM(s.revenue) - SUM(s.units * p.unit_cost) AS gross_profit,
       ROUND(100.0 * (SUM(s.revenue) - SUM(s.units * p.unit_cost))
             / SUM(s.revenue), 1)             AS margin_pct
FROM sales AS s
INNER JOIN products AS p
        ON s.product = p.product
GROUP BY s.category
ORDER BY gross_profit DESC;

s and p are table aliases, short names that tell SQL which table each column comes from (both tables have product, category and unit_price). Multiplying by 100.0 instead of 100 forces decimal division; in SQLite, dividing two integers drops the decimals.

Fitness has the lowest margin (43.9%) because adjustable dumbbells cost $176 to buy and sell for $299. That's the kind of insight you can't get from the sales table alone.

LEFT JOIN: find what's missing

An INNER JOIN keeps only rows that match in both tables. A LEFT JOIN keeps every row from the left table, and fills the right side with NULL when there's no match. That makes it perfect for finding gaps, such as products that never sold:

SELECT p.product,
       COUNT(s.order_id)          AS orders,
       COALESCE(SUM(s.revenue), 0) AS revenue
FROM products AS p
LEFT JOIN sales AS s
       ON p.product = s.product
GROUP BY p.product
ORDER BY revenue ASC
LIMIT 3;

The Desk Lamp is in the catalog but had zero sales in 2025. An INNER JOIN would have hidden it completely. COALESCE replaces the NULL sum with 0, and COUNT(s.order_id) counts only real matches; COUNT(*) would count the Desk Lamp's NULL row and wrongly show 1 order.

JOIN traps that change your totals

SELECT state, COUNT(*) AS orders, SUM(revenue) AS revenue
FROM sales
WHERE region = 'South'
GROUP BY state
HAVING SUM(revenue) > 4000
ORDER BY revenue DESC;

SELECT s.channel,
       SUM(s.revenue) AS revenue,
       SUM(s.units * (s.unit_price - p.unit_cost)) AS gross_profit,
       ROUND(100.0 * SUM(s.units * (s.unit_price - p.unit_cost))
             / SUM(s.revenue), 1) AS margin_pct
FROM sales AS s
INNER JOIN products AS p ON s.product = p.product
GROUP BY s.channel
ORDER BY gross_profit DESC;

Summary

• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse rows into one value; GROUP BY does it per group.
• Every SELECT column must be grouped or aggregated; SQLite won't always warn you.
• WHERE filters rows before grouping; HAVING filters groups after.
• SQL runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, which explains where aliases work.
• INNER JOIN keeps matches (gross profit: $42,514); LEFT JOIN keeps everything on the left and reveals gaps like the unsold Desk Lamp.
• Check row counts and totals before and after every join.

Next lesson: Data Visualization: How to Choose the Right Chart. You'll turn these query results into charts that make the main finding obvious in seconds.