SQL: Aggregate Functions

Active learning works for SQL aggregate functions because students need to see how calculations change with different data groupings. Hands-on execution reveals why COUNT(column) and COUNT(*) differ, and why mixing aggregates with non-aggregates fails without GROUP BY. These moments of direct observation stick longer than theory alone.

• Secondary 4
• Computing
• 4 activities
• 25 min–45 min

Learning Objectives

1. Calculate the total number of students in each class using the COUNT aggregate function.
2. Determine the average score for each subject using the AVG aggregate function.
3. Identify the highest and lowest scores for a specific exam using the MAX and MIN aggregate functions.
4. Construct SQL queries that combine aggregate functions with GROUP BY to summarize data by category.
5. Analyze scenarios to select the most appropriate aggregate function for a given analytical question.

Problem-Based Learning

⏱ 30 min·Pairs

Pair Query Relay: Aggregate Scenarios

Provide a dataset of student scores. Pairs alternate writing and running queries to answer prompts like 'Find AVG score per subject' or 'COUNT students above 80'. Switch roles after each query, then share correct results with the class.

Prepare & details

Explain how aggregate functions summarize data in meaningful ways.

Facilitation Tip: During Pair Query Relay, have students alternate writing queries and explaining their next step before running it, building verbal reasoning skills.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 45 min·Small Groups

Small Group: Sales Database Analysis

Give groups a sales database. They write queries using SUM for totals, AVG for averages per region, and GROUP BY for category summaries. Groups create a one-page report with results and present to class.

Prepare & details

Analyze scenarios where each aggregate function would be most appropriate.

Facilitation Tip: In Small Group Sales Database Analysis, assign each group a different function to master, then rotate findings so all students see each function in action.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 25 min·Whole Class

Whole Class: Function Match-Up

Display scenarios on the board, such as 'highest temperature' or 'total goals scored'. Class votes on best aggregate, then tests queries on a shared demo database projected for all to see.

Prepare & details

Construct SQL queries using aggregate functions to answer specific analytical questions.

Facilitation Tip: For Function Match-Up, ask students to justify why each function pair belongs together before revealing the answer, reinforcing conceptual connections.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 35 min·Individual

Individual: Custom Dataset Challenge

Students import their own small dataset, like sports stats. They independently write three aggregate queries to summarize it, then pair up to validate and refine each other's work.

Prepare & details

Explain how aggregate functions summarize data in meaningful ways.

Facilitation Tip: During the Individual Custom Dataset Challenge, provide starter code with intentional errors for students to fix, reinforcing careful syntax habits.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Teaching This Topic

Teach this topic by starting with ungrouped aggregates to show single-row results, then introduce GROUP BY gradually with visual comparisons. Use error-first demos to show students why SQL rejects mixing non-aggregates without GROUP BY. Research shows students learn aggregates best when they see the data shrink and expand with each GROUP BY clause, so emphasize the shape of results tables over memorizing syntax.

What to Expect

Successful learning shows when students confidently choose the right aggregate for a question, write correct GROUP BY clauses, and explain why NULLs affect COUNT(column). They should debug their own queries and help peers adjust syntax during group work.

Watch Out for These Misconceptions

Common Misconception

During Pair Query Relay, watch for students who assume COUNT(column) and COUNT(*) always return the same number.

What to Teach Instead

Ask pairs to insert a row with a NULL in the counted column, re-run both queries, and compare outputs to see the difference firsthand.

Common Misconception

During Function Match-Up, watch for students who believe aggregates can be mixed freely with non-aggregates.

What to Teach Instead

Provide a table with a GROUP BY clause that mixes a non-aggregate column, run it to show the error, then have students fix it by either adding GROUP BY or removing the non-aggregate.

Common Misconception

During Small Group Sales Database Analysis, watch for students who think GROUP BY applies only to numeric columns.

What to Teach Instead

Prompt groups to GROUP BY a text column like product category, run the query, and observe how SUM or AVG behaves with categorical data.

Assessment Ideas

Exit Ticket

After the Pair Query Relay, collect each student’s final query and have them write a one-sentence explanation of why they chose their function for the scenario.

Quick Check

During Small Group Sales Database Analysis, circulate and ask each group to show you their AVG and MAX queries side-by-side, then explain how the results differ when grouped by date versus product.

Discussion Prompt

After Function Match-Up, ask students to vote on which function pair was hardest to match, then facilitate a short discussion on what made the grouping tricky, reinforcing the GROUP BY rule.

Peer Assessment

During the Individual Custom Dataset Challenge, have students swap queries with a partner to debug and annotate syntax errors before final submission.

Extensions & Scaffolding

• Challenge students to write a single query that finds both the total sales and the highest-selling product in one pass using subqueries.
• For students who struggle, provide partial GROUP BY queries with missing clauses and ask them to complete the syntax before running.
• Allow advanced groups to explore window functions like OVER() to compare running totals against grouped aggregates, connecting this unit to later topics.

Key Vocabulary

COUNT	An SQL aggregate function that returns the number of rows that match a specified criterion. It can count all rows or only non-null values in a specific column.
SUM	An SQL aggregate function that calculates the total sum of values in a numeric column. It ignores NULL values.
AVG	An SQL aggregate function that computes the average value of a numeric column. It excludes NULL values from the calculation.
MIN	An SQL aggregate function that returns the smallest value from a selected column. It can be used with numeric, character, and date data.
MAX	An SQL aggregate function that returns the largest value from a selected column. It can be used with numeric, character, and date data.