Aggregate Functions

How many innings were played this season? What is the total of all runs? What was the best score? Questions like these do not want a list of rows — they want one number that summarises many rows. That is the job of aggregate functions.

All examples in this chapter use the innings table — 12 innings by 4 players. Look closely: it has ties (73, 59 and 45 each appear twice) and one NULL (MS Dhoni did not bat on 2025-03-31). Both matter a lot in this chapter.

Definition

An aggregate function performs a calculation on a set of values (many rows) and returns a single summary value. Except for COUNT(*), aggregate functions ignore NULL values.

• Set of values — the input is a whole column (or a group of rows, see GROUP BY), not one cell.
• Single summary value — 12 rows go in, 1 value comes out. The detail rows are "collapsed".
• Ignore NULL — a NULL means "unknown / missing", so SUM, AVG, MIN, MAX and COUNT(column) simply skip it. It is not treated as 0.

The five core aggregate functions

Syntax

SELECT
    AGGREGATE_FUNCTION(column_name) AS alias
FROM table_name
[WHERE condition];

Example — summarise the whole table

SELECT
    COUNT(*)  AS innings,
    SUM(runs) AS total,
    AVG(runs) AS average,
    MAX(runs) AS best,
    MIN(runs) AS lowest
FROM innings;

How it works: 12 rows collapse into one row. SUM adds the 11 known scores = 645. AVG = 645 ÷ 11 (not 12!) because Dhoni's NULL is ignored in both the total and the count. MIN is 0 (Rohit's duck) — 0 is a real value, NULL is not.

Note — SQL Server integer average: runs is an INT column. In SQL Server, AVG of an integer column returns an integer, so the query above shows 58, not 58.636 (the playground, which uses SQLite underneath, shows the decimal). To get the decimal average in SQL Server, convert first: AVG(runs * 1.0) or AVG(CAST(runs AS DECIMAL(10,2))). From now on this chapter writes AVG(runs * 1.0) so both engines give the same answer.

Example — COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

SELECT
    COUNT(*)               AS all_rows,
    COUNT(runs)            AS with_runs,
    COUNT(DISTINCT runs)   AS distinct_scores,
    COUNT(DISTINCT player) AS players
FROM innings;

• COUNT(*) counts rows → 12.
• COUNT(runs) counts non-NULL values → 11 (Dhoni's NULL is skipped).
• COUNT(DISTINCT runs) counts different non-NULL values → 8 (82, 59, 73, 45, 76, 0, 26, 107 — the ties count once).

Example — MIN and MAX on text and dates

SELECT
    MIN(player)     AS first_name_az,
    MAX(player)     AS last_name_az,
    MIN(match_date) AS first_match,
    MAX(match_date) AS last_match
FROM innings;

For text, MIN/MAX use alphabetical order; for dates, earliest / latest. SUM and AVG would fail on these columns — they need numbers.

Aggregates on an empty set

If no rows match (e.g. WHERE team = 'KKR' — KKR has no innings), COUNT(*) returns 0, but SUM, AVG, MIN, MAX return NULL (there is nothing to add or compare). Wrap with ISNULL(SUM(runs), 0) if you want 0.

Common mistakes

• ❌ Thinking AVG(runs) = total ÷ number of rows → ✅ it is total ÷ non-NULL values (645 ÷ 11, not 645 ÷ 12).
• ❌ Using COUNT(runs) to count rows → ✅ use COUNT(*); COUNT(column) silently drops NULLs.
• ❌ Mixing aggregates and plain columns: SELECT player, SUM(runs) FROM innings → ✅ add GROUP BY player (next topic).
• ❌ WHERE SUM(runs) > 100 → ✅ aggregates cannot be used in WHERE; filter groups with HAVING.
• ❌ Forgetting integer division in SQL Server: AVG(int_column) → ✅ AVG(int_column * 1.0).

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: What is the difference between COUNT(*), COUNT(1) and COUNT(column)? A: COUNT(*) and COUNT(1) both count all rows (no performance difference in modern engines). COUNT(column) counts only rows where that column is not NULL. On our table: 12, 12 and 11.