GROUP BY & Aggregate Functions

"Total runs per country?" "How many players does each team have?" These questions don't want individual rows — they want one summary row per group. That is what GROUP BY does, together with aggregate functions.

Definitions

An aggregate function performs a calculation on many rows and returns one single value: COUNT, SUM, AVG, MIN, MAX.

GROUP BY combines rows that have the same value in the grouping column(s) into one group, so that aggregate functions are calculated separately for each group. The result has one row per group.

Syntax

SELECT group_column, AGGREGATE(column) AS alias
FROM table_name
GROUP BY group_column;

Example — aggregates without GROUP BY (whole table = one group)

SELECT COUNT(*)  AS total_players,
       SUM(runs) AS total_runs,
       MIN(runs) AS lowest,
       MAX(runs) AS highest
FROM players;

Without GROUP BY the whole table is treated as one group, so you get exactly one row.

Example — total runs per country

SELECT country, SUM(runs) AS total_runs
FROM players
GROUP BY country;

Execution order: 1️⃣ FROM players → 2️⃣ GROUP BY country makes three groups — India (Kohli, Rohit, Dhoni, Bumrah), England (Buttler), Australia (Warner) → 3️⃣ SELECT calculates SUM(runs) once per group: India = 750 + 500 + 350 + 20 = 1620.

Example — players per team

SELECT team, COUNT(*) AS players
FROM players
GROUP BY team;

COUNT(*) counts the rows inside each group — MI has two (Rohit and Bumrah).

Note: Group order in the result is not guaranteed unless you add ORDER BY (e.g. ORDER BY total_runs DESC).

The golden rule of GROUP BY

Every column in the SELECT list must be either in GROUP BY or inside an aggregate function. Why? After grouping, India is one row — SQL can't show "the" name for India because there are four names.

-- ❌ SQL Server error 8120:
-- Column 'players.name' is invalid in the select list because it is not
-- contained in either an aggregate function or the GROUP BY clause.
SELECT country, name, SUM(runs)
FROM players
GROUP BY country;

Note: The browser playground (SQLite) is lenient and runs that query, picking a random name. Real SQL Server rejects it — always follow the golden rule.

AVG on whole numbers

In SQL Server, AVG of an INT column returns an INT (the decimals are cut off): AVG(runs) on players gives 528, not 528.33. To get decimals, convert first: AVG(runs * 1.0) or AVG(CAST(runs AS DECIMAL(10, 2))). (The playground shows 528.333…, because SQLite always averages in decimals.)

Common mistakes

• ❌ Selecting a non-grouped, non-aggregated column (see golden rule). ✅ Add it to GROUP BY or wrap it in an aggregate.
• ❌ Expecting COUNT(column) to count NULLs. ✅ Only COUNT(*) counts every row; COUNT(runs) skips NULLs (innings: 12 rows, 11 non-NULL runs).
• ❌ Using an aggregate in WHERE: WHERE SUM(runs) > 800. ✅ Filter groups with HAVING (next topic).

Interview tip

Q: What is the difference between COUNT(*) and COUNT(column)? A: COUNT(*) counts all rows in the group, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL. All aggregates except COUNT(*) ignore NULLs.