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.
Understanding SQL Aggregate Functions & GROUP BY
A grouped query has a useful logical order. SQL first finds the table named in FROM. It then removes rows that fail WHERE.
Next, it places the remaining rows into groups. The aggregate calculations happen within each group. HAVING removes whole groups based on those results.
Finally, SELECT chooses what to display, and ORDER BY can sort the finished result. This order explains a frequent mistake.
A condition about one sale, one student, or one event belongs in WHERE. A condition about a group total or group average belongs in HAVING.
The definition of a group matters more than students often expect. Grouping by department produces one result for each department. Grouping by department and year produces a separate result for every department-year combination.
Adding one more grouping column can greatly increase the number of rows returned. This is called changing the level of detail, or granularity. Before writing a query, state clearly what one output row represents.
It might represent one class, one customer, one month, or one class during one month. That sentence helps prevent incorrect grouping.
NULL needs careful thought because it usually means unknown, missing, or not recorded. It is not the same as zero. If a student has no recorded score, including zero in an average would lower the result as if the student earned zero.
Most aggregates ignore NULL values, so an average uses only known scores. This can be useful, but it can hide missing data.
Comparing COUNT star with COUNT of a particular column reveals how many records have a missing value in that column. When needed, SQL functions such as COALESCE can replace NULL with a chosen value, but that choice should match the meaning of the data.
Real data often comes from several related tables, which creates another important risk. A JOIN can repeat a row when one item matches several rows in another table. For example, joining orders to order items repeats each order once for every item it contains.
Summing an order total after that join may count the same order more than once. Check the relationship before aggregating. Ask whether each row represents an order, an item, a person, or an event.
Test a query on a small set of records and calculate one group by hand. Sort results to spot unexpected groups, and give aggregate results clear aliases such as total_sales or average_score. These habits make summaries easier to read, check, and trust.