GROUP BY and HAVING

Split a summary across categories — and filter which categories survive

The beer_db has 25 styles. You already know how to aggregate all 400 beers into one row. GROUP BY lets you produce one aggregate row per style — 25 summary rows instead of one.\n\nThe common mistake is trying to use an aggregate function inside a WHERE clause. WHERE runs before grouping — it has no groups to refer to yet. HAVING runs after GROUP BY and can reference aggregate results.\n\nThe logical order matters: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This is not the order you write the clauses — it is the order PostgreSQL evaluates them.

GROUP BY — One Row Per Category

-- Average ABV per style
SELECT style, ROUND(AVG(abv), 2) AS avg_abv, COUNT(*) AS beer_count
FROM beers
GROUP BY style
ORDER BY avg_abv DESC;

Every column in SELECT that is not inside an aggregate function must appear in GROUP BY.

HAVING — Filter Groups After Aggregation

-- Only styles that have more than 10 beers AND average ABV above 6%
SELECT style, COUNT(*) AS cnt, ROUND(AVG(abv), 2) AS avg_abv
FROM beers
GROUP BY style
HAVING COUNT(*) > 10
   AND AVG(abv) > 6.0
ORDER BY avg_abv DESC;

WHERE vs HAVING — Critical Distinction

-- WHERE: filter rows before grouping
-- HAVING: filter groups after grouping

-- Find styles where the average ABV (of beers above 4%) exceeds 7%
SELECT style, ROUND(AVG(abv), 2) AS avg_abv
FROM beers
WHERE abv > 4.0            -- rows filter: exclude low-ABV beers first
GROUP BY style
HAVING AVG(abv) > 7.0      -- group filter: only high-average styles
ORDER BY avg_abv DESC;

GROUP BY Multiple Columns

-- Count beers per country + style combination
SELECT br.country, b.style, COUNT(*) AS beer_count
FROM beers b
INNER JOIN breweries br ON b.brewery_id = br.id
GROUP BY br.country, b.style
ORDER BY br.country, beer_count DESC;

GROUP BY

Collapses multiple rows that share the same value(s) in the specified column(s) into a single group. Each group becomes one row in the output. Every SELECT column that is not wrapped in an aggregate function must appear in the GROUP BY clause — otherwise PostgreSQL does not know which row's value to use for that column.

HAVING

A filter clause that runs after GROUP BY, allowing you to include or exclude groups based on aggregate conditions. It is syntactically similar to WHERE but semantically different: WHERE filters individual rows before grouping, HAVING filters groups after aggregation. You can reference aggregate functions in HAVING but not in WHERE.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. GROUP BY and HAVING exercises use beers and breweries.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📦 GROUP BY: Average ABV per Style

Write a GROUP BY query that shows each beer style with its beer count and average ABV. Order by average ABV descending so the strongest styles appear first.

SELECT style, COUNT(*) AS beer_count, ROUND(AVG(abv), 2) AS avg_abv FROM beers GROUP BY style ORDER BY avg_abv DESC LIMIT 10;

style | beer_count | avg_abv -----------------+------------+--------- Imperial Stout | 11 | 9.80 Double IPA | 15 | 9.27 Belgian Tripel | 12 | 8.43 Baltic Porter | 9 | 7.59 Belgian Dubbel | 17 | 6.99 American IPA | 14 | 6.25 American Stout | 19 | 6.15 Märzenbier | 14 | 6.04 Saison | 11 | 6.03 American Porter | 13 | 5.76 (10 rows)

🚫 HAVING: Only Styles with High Average ABV

Add HAVING to the previous query to show only styles where the average ABV exceeds 7.0%. Remember: HAVING filters groups, not rows — it runs after GROUP BY.

SELECT style, COUNT(*) AS beer_count, ROUND(AVG(abv), 2) AS avg_abv FROM beers GROUP BY style HAVING AVG(abv) > 7.0 ORDER BY avg_abv DESC;

⚖️ WHERE + GROUP BY + HAVING Together

Combine all three: use WHERE to exclude beers below 4% ABV before grouping, GROUP BY style, then HAVING to keep only styles with more than 10 beers in the filtered set. This demonstrates the logical order: WHERE → GROUP BY → HAVING.

SELECT style, COUNT(*) AS beer_count, ROUND(AVG(abv), 2) AS avg_abv FROM beers WHERE abv >= 4.0 GROUP BY style HAVING COUNT(*) > 10 ORDER BY beer_count DESC, avg_abv DESC;

style | beer_count | avg_abv --------------------------+------------+--------- Munich Helles | 24 | 4.98 English Brown Ale | 22 | 4.80 Dry Irish Stout | 21 | 4.48 Blonde Ale | 20 | 4.83 American Stout | 19 | 6.15 Weissbier | 19 | 4.97 ... Amber Ale | 11 | 5.08 (22 rows)

🌍 GROUP BY Multiple Columns: Country + Style

Group by two columns — brewery country and beer style — to see how many beers each country produces per style. Join breweries to get the country. Show only combinations with at least 2 beers.

SELECT br.country, b.style, COUNT(*) AS beer_count FROM beers b INNER JOIN breweries br ON b.brewery_id = br.id GROUP BY br.country, b.style HAVING COUNT(*) >= 2 ORDER BY br.country, beer_count DESC LIMIT 10;

Lab complete! You can now write full GROUP BY + HAVING queries:\n\n\n WHERE → ✅ filters rows before grouping\n GROUP BY → ✅ one aggregate row per unique value\n HAVING → ✅ filters groups after aggregation\n ORDER → ✅ sorts the final grouped result\n

Enable JavaScript to run the live terminal and track your progress.