Aggregate Functions: Summarizing Data with COUNT, SUM, AVG
SQL uses a single equal sign (=) for equality testing in WHERE clauses, not the double equal sign (==) used in Python.
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.
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
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.
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?
Test row A: The condition is true, so row A remains.
Test row B: The condition is false, so row B is filtered out.
Test row C: The condition is true, so row C remains.
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.
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
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
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.
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
| Context | Equality syntax | Use described in the material |
|---|---|---|
| SQL WHERE clause | = | Equality testing |
| Python | == | Double equal sign used for equality testing |
Writing == when testing equality in a SQL WHERE clause
The material specifies that SQL uses a single equal sign for equality testing in WHERE clauses.
Fix:
Use = for equality testing in a SQL WHERE clause.Assuming WHERE and ORDER BY perform the same job
WHERE filters rows, while ORDER BY sorts the filtered results by a specified column.
Fix:
Use WHERE to select rows and ORDER BY to organize the rows that remain.Combining conditions without checking the logical operator
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.
Fix:
Identify each comparison and then apply the meaning of the connecting logical operator.
Practice the Query Flow
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?
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
- WHERE tests a condition against each row and returns only rows for which the condition is true.
- Comparison operators and the logical operators AND and OR let you construct filtering conditions.
- COUNT, SUM, and AVG are aggregate functions that summarize multiple rows.
- SQL uses a single equal sign for equality testing in a WHERE clause; Python uses a double equal sign for equality testing.
- ORDER BY sorts the filtered results by a specified column and is applied after WHERE filtering.
Key Takeaways
- 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.