GROUP BY and HAVING in SQL Server: The Rule That Trips Everyone Up

Aggregate functions get far more useful once you can compute them per-group instead of across the whole table — “total funding” is fine, but “total funding per industry” is what a real report actually needs. This is also the lesson where a genuinely confusing rule shows up for the first time, so we’ll spend real time on why it exists, not just what it says.

One Row Per Group

SELECT industry, COUNT(*) AS startup_count, AVG(funding_usd) AS avg_funding
FROM dbo.Startup
GROUP BY industry
ORDER BY avg_funding DESC;

Instead of one row per startup, you get one row per distinct industry value, each with its own COUNT and AVG computed only from the rows in that group.

Grouping by More Than One Column

SELECT industry, founded_year, COUNT(*) AS count_that_year
FROM dbo.Startup
GROUP BY industry, founded_year;

Each unique combination of industry and founded_year becomes its own group — grouping by more columns always produces more (or equal), never fewer, groups than grouping by one.

HAVING: Filtering Groups, Not Rows

SELECT industry, SUM(funding_usd) AS total_funding
FROM dbo.Startup
GROUP BY industry
HAVING SUM(funding_usd) > 15000000;

The Pipeline, Visualized

This ordering (an extension of the FROM → WHERE → SELECT → ORDER BY pipeline from Chapter 3) is the key to understanding both HAVING and the SELECT-column rule below: WHERE filters raw rows before grouping happens; HAVING filters the already-formed groups after. They operate on fundamentally different things, which is exactly why you need both, and why they can’t substitute for each other.

The Rule That Trips Everyone Up

Every column in SELECT must either be in the GROUP BY list, or wrapped in an aggregate function:

-- FAILS: name is neither grouped nor aggregated
SELECT name, industry, AVG(funding_usd) FROM dbo.Startup GROUP BY industry;
-- Msg 8120: Column 'dbo.Startup.name' is invalid in the select list because it is
-- not contained in either an aggregate function or the GROUP BY clause.

Once rows collapse into groups, SQL Server has no single value to show for name — there could be several startups per industry, each with a different name. It has no way to know which one you want, so it refuses to guess. This is arguably the most useful error message to truly understand in this entire chapter, because the fix is always one of exactly two options:

-- Option 1: add the column to GROUP BY (now every industry+name combo is its own group)
SELECT name, industry, AVG(funding_usd) FROM dbo.Startup GROUP BY name, industry;

-- Option 2: aggregate it instead (pick one representative name per group, e.g. the first alphabetically)
SELECT industry, MIN(name) AS a_sample_name, AVG(funding_usd) FROM dbo.Startup GROUP BY industry;

Why WHERE Can’t Filter on an Aggregate

-- FAILS: WHERE runs before GROUP BY, so COUNT(*) doesn't exist yet at that point
SELECT industry, COUNT(*) AS c FROM dbo.Startup WHERE COUNT(*) > 2 GROUP BY industry;
-- Msg 147: An aggregate may not appear in the WHERE clause

-- HAVING runs after grouping, so the aggregate genuinely exists by then:
SELECT industry, COUNT(*) AS c FROM dbo.Startup GROUP BY industry HAVING COUNT(*) > 2;

Once you internalize the logical order — rows are filtered (WHERE), then collapsed into groups (GROUP BY), and only then do aggregate values exist to filter on (HAVING) — both of these rules stop feeling arbitrary and start feeling obvious.

Combining WHERE and HAVING in the Same Query

-- WHERE excludes rows before grouping; HAVING excludes groups after
SELECT industry, AVG(funding_usd) AS avg_funding
FROM dbo.Startup
WHERE founded_year >= 2019       -- only consider startups founded 2019+
GROUP BY industry
HAVING AVG(funding_usd) > 5000000; -- then only show industries averaging over $5M