Concepts / Aggregate Functions: Summarizing Data with COUNT, SUM, AVG

Aggregate Functions: Summarizing Data with COUNT, SUM, AVG

Think of a query as a two-stage process: first identify the rows that satisfy the WHERE condition, then summarize the resulting set with an aggregate function.

  • Programming
Interactive lab

Try it: SELECT, WHERE, ORDER BY, LIMIT

How a SELECT query tests every row against the WHERE condition (keeping only rows where it is TRUE, not FALSE or NULL), then picks columns, removes duplicates with DISTINCT, sorts with ORDER BY and cuts with LIMIT.

How it works

  1. FROM: start with every row of the table.
  2. WHERE: evaluate the condition on each row. Comparisons with NULL give NULL (unknown); NOT, AND and OR follow three-valued logic, with AND binding tighter than OR. Only rows where the whole condition is TRUE are kept.
  3. SELECT picks the listed columns; DISTINCT then drops result rows that repeat an earlier one.
  4. ORDER BY sorts by each key in turn, ASC or DESC; SQLite puts NULL first when ascending and last when descending.
  5. LIMIT n keeps only the first n rows of the sorted result.

Default run (13 steps): SELECT title, plays FROM track WHERE plays >= 95 AND rating >= 4 ORDER BY plays DESC LIMIT 2. FROM: start with all 8 rows of track. … Result: 2 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Ties in ORDER BY are broken by id here (by the selected columns with DISTINCT), and DISTINCT without ORDER BY keeps first-occurrence order; SQLite itself promises no order for ties. LIKE is case-insensitive for ASCII letters, as in SQLite.

Educational simulation

Loading the simulation…

Interactive lab

Try it: Aggregates, GROUP BY and HAVING

How COUNT, SUM, AVG, MIN and MAX summarise the rows that survive WHERE, how GROUP BY computes one summary per group of equal keys, how HAVING then keeps only some groups, and how each aggregate treats NULL.

How it works

  1. WHERE removes rows first; only the remaining rows are summarised.
  2. GROUP BY sends each row to the group of its key value; all NULL keys share one group. Without GROUP BY every row is in one group — even when there are no rows at all.
  3. Each aggregate runs once per group: COUNT(*) counts rows, COUNT(col) counts non-NULL values, SUM / AVG / MIN / MAX skip NULLs and return NULL when nothing is left. AVG is always a REAL number.
  4. HAVING tests each group's aggregate; a group is kept only when the test is TRUE.

Default run (18 steps): SELECT artist, SUM(plays) FROM track GROUP BY artist HAVING SUM(plays) > 200 ORDER BY artist. Start with the 8 rows of track. … Result: 2 rows, ordered by artist (NULL first).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One aggregate per query; groups are listed in key order (ORDER BY the group column, NULL first).

Educational simulation

Loading the simulation…

Start with the Matching Rows

A summary is only as meaningful as the rows included in it. In SQL, the WHERE clause tests a condition against each row. Only rows for which the condition is true are returned. Aggregate functions such as COUNT, SUM, and AVG summarize multiple rows, so understanding which rows qualify is an essential first step.

Key point

Think of a query as a two-stage process: first identify the rows that satisfy the WHERE condition, then summarize the resulting set with an aggregate function.

Filter Before Summarizing

All rowsrow A, row B, row CWHERE conditiontest each rowMatching rowsrow A, row Ctesttrue
Which rows remain after a WHERE condition is applied, and which rows are filtered out?

The WHERE clause does not summarize rows. It decides which rows are eligible to appear in the result. The condition is tested against each row individually. Rows that make the condition true remain in the returned set; rows that make it false do not.

Worked example

Selecting a Subset

Suppose a query uses a WHERE condition that is true for row A and row C but false for row B. Which rows are available after filtering?

  1. 1
    Test row A

    The condition is true, so row A remains.

  2. 2
    Test row B

    The condition is false, so row B is filtered out.

  3. 3
    Test row C

    The condition is true, so row C remains.

Result

The filtered result contains row A and row C. An aggregate function applied after this filtering summarizes those matching rows rather than the rows that were removed.

Build Precise WHERE Conditions

SQL WHERE clauses can use comparison operators to express conditions. The available comparisons described in this material are equality with =, less than with <, greater than with >, less than or equal to with <=, greater than or equal to with >=, and not equal to with !=. Logical operators combine conditions: AND requires the combined conditions to hold together, while OR allows either condition to make the combined expression true.

Each rowcandidate rowCondition AcomparisonANDboth conditionsMatching rowsreturned setCondition BcomparisonOReither conditiontesttestcombinecombinecombinecombinetruetrue
How do AND and OR change which rows satisfy a condition when multiple comparisons are combined?
Best practice

Read a compound WHERE condition in two stages. First identify each comparison separately. Then determine whether AND or OR connects them. This makes it easier to predict which rows will reach the result set before any aggregate summary is produced.

Summarize the Result Set

Matching rowsrows that pass WHERECOUNTsummary valueSummary valuesone result for eachfunctionSUMsummary valueAVGsummary valuesummarizesummarizesummarizeproduceproduceproduce
How do COUNT, SUM, and AVG transform a set of matching rows into summary values?
Definition

COUNT, SUM, and AVG are aggregate functions. They summarize multiple rows into summary values. The rows supplied to this summarization are the rows that remain after the relevant filtering condition has been applied.

Arrange Filtered Results

Filtered rowsrow B, row A, row CORDER BYspecified columnSorted rowsrow A, row B, row Corganizerearrange
How does ORDER BY rearrange the same returned rows according to a selected field?

ORDER BY sorts the filtered results by a specified column. It is applied after WHERE filtering. Therefore, ORDER BY changes the organization of the rows that were returned; it does not determine which rows pass the WHERE condition.

Key point

The order of ideas is important: WHERE selects rows, aggregate functions summarize multiple rows, and ORDER BY organizes filtered results by a specified column.

Avoid Equality Confusion

ContextEquality syntaxUse described in the material
SQL WHERE clause=Equality testing
Python==Double equal sign used for equality testing

SQL and Python use different equality syntax.

  • Common mistake

    Writing == when testing equality in a SQL WHERE clause

    What it looks like

    Using the Python-style double equal sign in SQL

    Why it fails

    The material specifies that SQL uses a single equal sign for equality testing in WHERE clauses.

    Do this instead
    Use = for equality testing in a SQL WHERE clause.
  • Common mistake

    Assuming WHERE and ORDER BY perform the same job

    What it looks like

    Treating ORDER BY as though it removes rows

    Why it fails

    WHERE filters rows, while ORDER BY sorts the filtered results by a specified column.

    Do this instead

    Use WHERE to select rows and ORDER BY to organize the rows that remain.

  • Common mistake

    Combining conditions without checking the logical operator

    What it looks like

    Reading AND and OR as interchangeable

    Why it fails

    AND and OR combine conditions differently: AND requires the combined conditions to hold together, while OR allows either condition to make the combined expression true.

    Do this instead

    Identify each comparison and then apply the meaning of the connecting logical operator.

Practice the Query Flow

MEDIUM

A query needs to return only rows that satisfy a condition, summarize those matching rows with an aggregate function, and then organize the filtered results by a specified column. Explain the role of WHERE, the aggregate function, and ORDER BY in that sequence. Also state which equality symbol belongs in the SQL WHERE condition.

Hints
  • Start by identifying which clause tests each row.
  • Next identify which operation summarizes multiple rows.
  • Finally identify which clause sorts the filtered results.
  • For SQL equality testing in WHERE, choose the single equal sign.

What do you think happens?

A WHERE condition is true for two rows and false for three rows. Which rows are available for an aggregate summary?

  • All five rows

  • Only the two rows for which the condition is true

  • Only the three rows for which the condition is false

Reveal answer
Answer:

Only the two rows for which the condition is true

WHERE tests the condition against each row, and only rows where the condition is true are returned. Aggregate functions summarize the matching result set.

Key Takeaways

  1. 1
    WHERE tests a condition against each row and returns only rows for which the condition is true.
  2. 2
    Comparison operators and the logical operators AND and OR let you construct filtering conditions.
  3. 3
    COUNT, SUM, and AVG are aggregate functions that summarize multiple rows.
  4. 4
    SQL uses a single equal sign for equality testing in a WHERE clause; Python uses a double equal sign for equality testing.
  5. 5
    ORDER BY sorts the filtered results by a specified column and is applied after WHERE filtering.

At a glance

  • Filter first: WHERE determines which rows are eligible.
  • Use comparison operators and AND or OR to express precise conditions.
  • Aggregate functions summarize the multiple rows that remain after filtering.
  • Use = for equality in a SQL WHERE clause, not Python's ==.
  • Use ORDER BY to organize the filtered results by a specified column.

Related Topics

Related concepts

More concepts starting with A