GROUP BY and HAVING: How to Summarize Rows Without Getting a Fake Answer

1. What GROUP BY actually does to your rows

Before the explanation: your table has 82,956 rows. You group it by genre and get 28 rows back. Where did the other 82,928 rows go, and can you still see any of them?

Here is the rule. GROUP BY sorts every row into a bucket, then replaces each bucket with exactly one output row. Nothing is deleted. The rows are still in there, collapsed. What changes is what one row of your result means.

That last sentence is the whole idea. Before grouping, one row was one game. After grouping by genre, one row is one genre. The word for this is grain, and saying the new grain out loud before you write anything else prevents most of the mistakes on this page.

An aggregate function is how you get a value back out of a collapsed bucket. COUNT(*) asks how many rows are in this bucket. AVG(PctPositive) asks for the mean of that column across the bucket. SUM, MIN and MAX work the same way. Each one takes many values and returns one, which is exactly what a single output row needs.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre;

That returns 28 rows, one per genre. Action has 35,149 games and averages 75.4% positive. Adventure has 17,486 and averages 77.7%. The 82,956 original rows are all still accounted for, because every game landed in exactly one bucket.

2. Which columns you can select, and the rule behind it

Before the explanation: you group by genre and also select the game's name. The database either errors or hands you one name out of 35,149. Which name should it give you?

There is no sensible answer, which is why the rule exists. After grouping, every column in your SELECT must be one of two things.

• A column you grouped by. It is safe because every row in the bucket has the same value for it. All 35,149 Action games have the genre Action.
• An aggregate. It is safe because it turns the whole bucket into one value on purpose.

Anything else is a column with 35,149 different values and one slot to put them in. Most databases refuse. PostgreSQL and SQL Server raise an error naming the column. SQLite and older MySQL settings do something worse: they pick a value arbitrarily and return it without comment, so you get a real-looking name attached to a genre-wide average.

-- Errors on PostgreSQL, returns a misleading row on SQLite
SELECT PrimaryGenre, Name, AVG(PctPositive)
FROM games
GROUP BY PrimaryGenre;

Say why that query has no correct answer, in your own words, before reading on. If you can state the reason, the error message will never confuse you again.

The reason is that Name is at the wrong grain. It describes one game, and the row now describes a genre. When you genuinely want the top name per group, that is a different tool, and it is the subject of window functions.

Grouping by more than one column works the same way, with a finer bucket. GROUP BY PrimaryGenre, ReleaseYear makes one row per genre per year, and both of those columns are then safe to select.

3. WHERE against HAVING, and why the order is forced

Before the explanation: you want genres whose average rating is above 80. You try putting that condition in WHERE and the database refuses. Why can it not simply work it out?

Because when WHERE runs, the average does not exist yet. This is not a style preference. It is the order the database evaluates the clauses in, and that order is fixed.

So WHERE COUNT(*) >= 500 is asking step two for a number that step four invents. The error is the database telling you the truth about its own sequence.

The practical version is short. If your condition is about one row, it goes in WHERE. If it is about a whole group, it goes in HAVING.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
WHERE TotalReviews >= 500        -- about one game
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500           -- about a whole genre
ORDER BY avg_rating DESC;

Both filters in one query, and they are doing different jobs. The WHERE line says a game needs enough reviews for its own rating to mean anything, which drops the catalogue from 82,956 games to 10,507. The HAVING line says a genre needs enough games for its average to mean anything.

4. The small-group problem, and the line that fixes it

Before the explanation: which genre in a catalogue of 82,956 games do you think has the highest average rating? Commit to a guess before you read the answer.

It is Accounting, at 79.5% positive. Ahead of Adventure, ahead of RPG, ahead of everything. The query is correct, the data is real, and the answer is worthless, because there are eleven accounting games in the entire catalogue.

Here is the ranking without a floor.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre
ORDER BY avg_rating DESC;

Three of the top five are categories with fewer than 25 items. This is not bad luck. It is arithmetic: a mean over eleven values moves freely, and a mean over seventeen thousand barely moves at all. So the extremes of any ranking fill up with the smallest groups every time, in both directions.

One line fixes it.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500
ORDER BY avg_rating DESC;

That floor removed 20 of the 28 genres and produced the first ranking anyone could act on. The spread also collapsed, from a top of 79.5 down to 77.7, which is the honest picture: genres are much more alike than the first table suggested.

The number 500 is a judgement, not a fact, and it has to be defensible. Pick it from the data rather than from taste, write down why, and check whether the ranking survives a different choice. Choosing thresholds from the data covers how to do that honestly.

Now picture the last summary query you shipped. If you added HAVING COUNT(*) >= 30 to it, would the top of the result change? That is the hinge of this guide, and for most real reports the answer is yes.

5. The full before and after

Same table, same question: which genres do players rate highest?

Before

SELECT PrimaryGenre, AVG(PctPositive) AS avg_rating
FROM games
GROUP BY PrimaryGenre
ORDER BY avg_rating DESC;

Three problems, none of which produce an error. Games with three reviews count the same as games with thirty thousand. Genres with eleven games count the same as genres with thirty-five thousand. And the reader cannot see either problem, because the group sizes are not on the screen.

After

-- ============================================================
-- STEP 3: Which genres do players actually rate highest?
-- WHY: A rating is only meaningful if enough people gave one,
--      and a genre average is only meaningful if the genre has
--      enough games. Both floors are here for that reason.
-- COVERAGE: 10,507 of 82,956 games have 500+ reviews. Eight of
--      28 genres clear the 500-game floor. The other 20 are
--      reported separately, not silently dropped.
-- ============================================================
--SELECT the genre, how many games are behind the number,
--   and their average positive rating:
--   COUNT(*)          = games in this genre that passed WHERE
--   AVG(PctPositive)  = mean positive rating across them
--FROM the full game catalogue
--WHERE the game itself has at least 500 reviews
--GROUP BY genre, so each genre collapses to one row
--HAVING at least 500 such games, so the average is stable
--ORDER BY average rating, highest first
SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
WHERE TotalReviews >= 500
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500
ORDER BY avg_rating DESC;

The group size is now a visible column, so a reader can judge the average for themselves. Both floors are stated with a reason. The coverage line names what was excluded. The comment format is the one from how to comment SQL so it teaches.

6. Edge cases that break summary queries in real data

Before the explanation: a genre you know exists is missing from your grouped result entirely. Not zero. Absent. What would you check first?

Six things that each cost someone an afternoon.

A WHERE filter emptied the group. This is the answer to the prequestion. Grouping can only produce a bucket for rows that survived WHERE. If every game in a genre had fewer than 500 reviews, that genre has no rows to collapse and no output row at all. A group that should read zero is instead invisible, which is a different and more misleading thing.

COUNT(*) and COUNT(column) are not the same. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. On a column with missing values they return different numbers, and only one of them answers your question. COUNT in SQL goes through the whole family.

NULL becomes its own group. Rows with a missing genre collapse into a single bucket that usually prints as blank. It looks like a formatting glitch and it is really a count of your missing data, which is worth reading rather than hiding.

Aggregates skip NULL, and that moves your average. AVG divides by the number of non-null values, not by the number of rows. So an average over a column that is 30% empty is an average of the 70% who answered, which is a genuinely different claim.

You cannot use a SELECT alias in HAVING everywhere. HAVING avg_rating > 80 works in MySQL and SQLite and fails in PostgreSQL and SQL Server, because SELECT runs after HAVING. Repeating the full AVG(PctPositive) > 80 works everywhere.

Grouping after a join counts duplicates. If a join multiplied your rows, COUNT(*) now counts pairs rather than things. COUNT(DISTINCT id) is usually what you meant. This is the most common wrong number in real reporting, and SQL joins covers why it happens.