Click image to open full size
SQL Aggregate Functions & GROUP BY Cheat Sheet
A printable reference covering COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, and NULL handling in SQL for grades 9-12.
Related Worksheets
Study as Flashcards
SQL aggregate functions summarize many rows into one result, which is essential for finding totals, counts, averages, minimums, and maximums in data tables. GROUP BY lets you calculate those summaries separately for each category, such as each class, product, or team. This cheat sheet helps students remember the order, syntax, and rules needed to write correct summary queries. It is useful when learning databases, data analysis, and computer science problem solving. The main aggregate functions are COUNT(), SUM(), AVG(), MIN(), and MAX(). A GROUP BY clause groups rows that share the same value in one or more columns before the aggregate is calculated. WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. Any column in SELECT that is not inside an aggregate function usually must appear in GROUP BY.
Key Facts
- COUNT(*) returns the number of rows in a group, including rows with NULL values.
- COUNT(column_name) returns the number of non-NULL values in that column.
- SUM(column_name) adds all non-NULL numeric values in a group.
- AVG(column_name) calculates the mean of non-NULL numeric values using SUM(column_name) / COUNT(column_name).
- MIN(column_name) returns the smallest value and MAX(column_name) returns the largest value in a group.
- The basic grouped query pattern is SELECT group_column, aggregate_function(column) FROM table_name GROUP BY group_column;
- WHERE filters rows before GROUP BY, but HAVING filters grouped results after aggregate functions are calculated.
- A common rule is that every non-aggregated column in SELECT must also be listed in GROUP BY.
Vocabulary
- Aggregate Function
- A SQL function that combines values from multiple rows to produce one summary value.
- GROUP BY
- A SQL clause that collects rows with matching values so aggregate functions can be calculated for each group.
- COUNT
- An aggregate function that returns how many rows or non-NULL values match a query.
- HAVING
- A SQL clause that filters groups after aggregate calculations have been performed.
- WHERE
- A SQL clause that filters individual rows before grouping or aggregation happens.
- NULL
- A special database value that means missing, unknown, or not provided data.
Common Mistakes to Avoid
- Using WHERE with an aggregate condition, such as WHERE COUNT(*) > 5, is wrong because WHERE runs before aggregates are calculated. Use HAVING COUNT(*) > 5 instead.
- Selecting a non-aggregated column that is not in GROUP BY is wrong because SQL does not know which value from the group to show. Add the column to GROUP BY or use an aggregate function.
- Assuming COUNT(column_name) counts every row is wrong because it ignores NULL values in that column. Use COUNT(*) when you need the total number of rows.
- Forgetting that AVG(column_name) ignores NULL values is wrong when missing data should count as zero. Use a replacement function such as COALESCE(column_name, 0) if the database supports it.
- Grouping by the wrong column is wrong because it changes the meaning of the summary. Check whether the question asks for totals by student, class, date, category, or another field.
Practice Questions
- 1 A table named Orders has columns order_id, customer_id, and total. Write a SQL query to find the total amount spent by each customer.
- 2 A table named Scores has columns student_id, class_name, and score. Write a SQL query to find the average score for each class.
- 3 A table named Products has columns category, product_name, and price. Write a SQL query to show each category where the average price is greater than 50.
- 4 Explain why HAVING AVG(score) >= 90 is used instead of WHERE AVG(score) >= 90 in a grouped query.