GROUP BY with Aggregates

One season total is useful, but usually you want a total per team or per player — "how many runs did each franchise score?". GROUP BY splits the rows into groups and runs the aggregate function once per group.

Definition

GROUP BY divides the rows of a result into groups of rows that share the same value(s) in the listed column(s), and returns one row per group. Aggregate functions in the SELECT are then calculated separately for each group.

• Groups — all rows with team = 'RCB' form one group, all 'MI' rows another, and so on.
• One row per group — 12 innings by 4 teams become 4 rows. The individual innings (detail rows) are lost in the output.
• Separately for each group — SUM(runs) is no longer the season total; it is the total of the rows inside each group.

HAVING filters groups after aggregation, the way WHERE filters rows before aggregation.

Syntax

SELECT
    group_column,
    AGGREGATE_FUNCTION(column_name) AS alias
FROM table_name
[WHERE row_condition]
GROUP BY group_column
[HAVING group_condition]
[ORDER BY ...];

The golden rule: every column in SELECT must either be in the GROUP BY or be inside an aggregate function. SQL cannot show "the player" of the RCB group unless you group by player too — the group has three rows, which one should it show?

Example — one row per team

SELECT
    team,
    COUNT(*)  AS innings,
    SUM(runs) AS total,
    MAX(runs) AS best
FROM innings
GROUP BY team;

How it works: SQL makes 4 buckets (CSK, MI, RCB, RR), each with 3 innings, then runs COUNT, SUM and MAX inside each bucket. CSK's total is 26 + 45 = 71 — Dhoni's NULL is ignored. GROUP BY collapses rows: 12 detail rows → 4 summary rows.

Example — per-player report card

SELECT
    player,
    COUNT(*)                  AS innings,
    COUNT(runs)               AS batted,
    SUM(runs)                 AS total,
    ROUND(AVG(runs * 1.0), 1) AS avg_runs
FROM innings
GROUP BY player
ORDER BY total DESC;

Notice Dhoni: 3 innings but only 2 batted, and his average is 71 ÷ 2 = 35.5.

Example — HAVING: only players above 150 runs

SELECT
    player,
    SUM(runs) AS total
FROM innings
GROUP BY player
HAVING SUM(runs) > 150
ORDER BY total DESC;

WHERE runs > 150 would check each innings (none is above 150). HAVING SUM(runs) > 150 checks each group's total.

GROUP BY vs window functions — a preview

GROUP BY is perfect for reports, but it throws away the detail rows. If you want to see each innings and the team total next to it, you need a window function — the subject of the rest of this chapter.

Common mistakes

• ❌ SELECT player, team, SUM(runs) FROM innings GROUP BY team → error in SQL Server: "Column 'innings.player' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." ✅ Add player to GROUP BY or remove it.
• ❌ WHERE SUM(runs) > 150 → ✅ HAVING SUM(runs) > 150.
• ❌ Using a SELECT alias in GROUP BY (GROUP BY total) in SQL Server → ✅ repeat the expression; GROUP BY runs before SELECT, so the alias does not exist yet. (ORDER BY can use the alias, because it runs last.)
• ❌ Expecting GROUP BY to sort the output → ✅ always add ORDER BY if order matters.

SQL Server vs MySQL / PostgreSQL

MySQL (with ONLY_FULL_GROUP_BY turned off) lets you select a non-grouped column and silently picks a random value from the group. SQL Server and PostgreSQL refuse — which is safer. MySQL and PostgreSQL also allow GROUP BY alias; SQL Server does not.

Interview tip

Q: What is the difference between WHERE and HAVING? A: WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after grouping and is normally used with aggregates like HAVING COUNT(*) > 2.