IB COMPUTER SCIENCE | A3.3.4 SQL Aggregate Functions (HL ONLY)

Lesson objective

Construct calculations with AVG, COUNT, SUM, MIN and MAX, using GROUP BY, WHERE and HAVING to produce and interpret useful reports.

Learn

A3.3.4 | SQL AGGREGATE FUNCTIONS (HL ONLY)

01 | TURN RECORDS INTO AN ANSWER

A teacher has assessment results but wants more than a list of names. How many students have a recorded score? What is the mean? Which class needs additional support? SQL can calculate these summaries directly from the records.

An aggregate function combines values from a set of rows into a summary value. Without GROUP BY, the selected rows are treated as one group for these calculations. With GROUP BY, SQL can produce one summary per class, category or other grouping.

The syllabus lists AVERAGE, COUNT, MAX, MIN and SUM. The average operation is normally written AVG(...) in SQL, not AVERAGE(...). We will use AVG in executable examples.

This is an HL lesson. To construct a calculation, choose the appropriate aggregate, identify its input rows and columns, and write the clauses that produce the requested report.

02 | READ THE DATA BEFORE CALCULATING

AssessmentID | PKStudentNameClassGroupScore
1MayaA80
2LeonA60
3AishaANULL
4NoahB90
5MeiB0
6RinB70
7IvoB80
CREATE TABLE Assessment (
    AssessmentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(60) NOT NULL,
    ClassGroup VARCHAR(10) NOT NULL,
    Score DECIMAL(5,2) CHECK (Score >= 0 AND Score <= 100)
);

One record represents one student’s result for this assessment. Aisha’s Score is NULL because it has not been recorded. Mei’s Score is 0, a known result. These are deliberately different.

There are seven records but only six numeric scores: 80, 60, 90, 0, 70 and 80. Their total is 380. Class A has three records with two known scores; Class B has four records with four known scores.

Score uses a decimal type so that fractional values and means have a suitable representation. Exact AVG return types and numeric behaviour depend on the DBMS and input type; verify these when using other schemas, particularly integer inputs.

03 | MATCH THE FUNCTION TO THE QUESTION

QuestionFunctionMeaning
How many records?COUNT(*)Count rows, including rows containing NULL fields.
How many recorded scores?COUNT(Score)Count non-null Score values.
What is the total?SUM(Score)Add the non-null scores.
What is the arithmetic mean?AVG(Score)Total of known scores divided by their count.
What is the lowest known score?MIN(Score)Smallest non-null score.
What is the highest known score?MAX(Score)Largest non-null score.

Each function receives an input expression inside parentheses, except that COUNT(*) uses the star to count rows. Choose based on what the question asks, not which command name looks familiar.

MIN and MAX can also work on supported ordered types such as dates, where a minimum can mean earliest and a maximum latest. Our worked calculations use numbers so their meaning is visible.

04 | COUNT ROWS OR COUNT RECORDED VALUES

SELECT COUNT(*) AS RecordCount,
       COUNT(Score) AS RecordedScoreCount
FROM Assessment;
RecordCountRecordedScoreCount
76

COUNT(*) counts every selected row, including Aisha’s row. It does not require every field in that row to be non-null. COUNT(Score) counts only rows whose Score expression is not NULL, so it returns six.

A zero score is present and must be counted. Mei contributes one to both counts. Aisha contributes one to COUNT(*) but nothing to COUNT(Score).

AS gives each calculated output a readable alias. RecordCount and RecordedScoreCount name result columns; they do not create new stored columns in Assessment.

05 | SUM ADDS VALUES, NOT RECORDS

SELECT SUM(Score) AS TotalScore
FROM Assessment;

The calculation is 80 + 60 + 90 + 0 + 70 + 80 = 380. Aisha’s missing score is ignored by SUM, and Mei’s known zero contributes zero to the total.

SUM(Score) answers “what is the combined score?”, whereas COUNT(Score) answers “how many scores are known?”. The two functions operate on the same known values but answer different questions.

Repeated equal values still contribute separately. Both 80 scores belong to different recorded results, so both belong in this total. Removing duplicates would change the meaning.

06 | AVG USES THE COUNT OF KNOWN VALUES

SELECT AVG(Score) AS MeanScore
FROM Assessment;

The arithmetic mean is 380 ÷ 6 = 63.333…. AVG ignores NULL scores, so its denominator is six known values, not all seven records.

Zero is included: Mei’s 0 contributes to the denominator even though it adds nothing to the numerator. Excluding it would overstate the mean. Treating the missing score as zero would instead understate the mean and assert something we do not know.

For a displayed report you might round to 63.33, but retain precision while calculating. Replacing AVG with SUM(Score) / COUNT(*) would give a different and inappropriate denominator here. Hand-written division can also introduce integer-division and zero-denominator issues depending on types and DBMS.

07 | MIN AND MAX RETURN VALUES, NOT WHOLE RECORDS

SELECT MIN(Score) AS LowestScore,
       MAX(Score) AS HighestScore
FROM Assessment;
LowestScoreHighestScore
090

MIN returns Mei’s known zero as the lowest score. MAX returns 90 as the highest. Neither result automatically includes the associated student’s name or identifier.

Do not write SELECT StudentName, MAX(Score) FROM Assessment and assume the name comes from the maximum-score row. A name is not determined by this ungrouped aggregate in a portable query. Some systems reject the query, while permissive behaviour can mislead.

To retrieve the matching record, an extension is to compare Score with a subquery that calculates the maximum:

SELECT StudentName, Score
FROM Assessment
WHERE Score = (SELECT MAX(Score) FROM Assessment)
ORDER BY StudentName, AssessmentID;

This returns Noah and 90 for our data. If several students tie at the maximum, all matching rows are returned. The calculation and the retrieval have separate roles.

08 | DISTINCT COUNTS DIFFERENT VALUES

SELECT COUNT(DISTINCT Score) AS DifferentScores
FROM Assessment;

The different known scores are 0, 60, 70, 80 and 90, so COUNT(DISTINCT Score) returns 5. Ordinary COUNT(Score) is 6, because the two 80 scores are separate recorded values.

COUNT(DISTINCT Score) ignores NULL and counts different non-null values. It does not count students. In other datasets, count a student identifier distinctly if the requirement asks how many different students are represented.

Similarly, AVG(DISTINCT Score) would average the different score values, not all students’ results. Here it gives 60 instead of 63.333…, so do not add DISTINCT to an average unless that is what the question means.

09 | EXPLORE A CALCULATION

Choose a scope, a row filter and a function. Predict the number of input rows and known scores, then build the result. Compare missing scores with a scope that selects no records at all.

This activity models fixed numeric data in the page. It displays equivalent SQL but is not a general SQL engine. Means are shown to two decimal places for readability; the SQL AVG expression itself is not rounded. The source records are unchanged.

10 | GROUP BY BUILDS ONE SUMMARY PER CLASS

SELECT ClassGroup, COUNT(*) AS RecordCount,
       COUNT(Score) AS RecordedScoreCount,
       SUM(Score) AS TotalScore, AVG(Score) AS MeanScore
FROM Assessment
GROUP BY ClassGroup
ORDER BY ClassGroup ASC;
ClassGroupRecordCountRecordedScoreCountTotalScoreMeanScore
A3214070
B4424060

GROUP BY ClassGroup collects the three A records into one group and the four B records into another. Each aggregate is then evaluated separately within its group.

For A, COUNT(*) is 3 but COUNT(Score) is 2, SUM is 140 and AVG is 70. For B, all four scores are known, so SUM is 240 and AVG is 60.

Grouping does not physically split the stored table into two new tables. It changes how this query produces summaries. ORDER BY makes the returned class order explicit; GROUP BY itself does not guarantee a sort order.

11 | WHAT MAY APPEAR BESIDE AN AGGREGATE?

A grouped result needs one meaningful value for each selected expression per group. ClassGroup is suitable because it defines the group. AVG(Score) is suitable because it summarises its score values.

StudentName is not a single value for Class A: Maya, Leon and Aisha are all present. Selecting an ungrouped StudentName beside the class average is therefore not a portable way to report a meaningful student.

Use SELECT ClassGroup, AVG(Score) ... GROUP BY ClassGroup for a class report. If the requirement needs one group per class and assessment type in another schema, group by both relevant fields.

Be clear about what each input row represents. If students have several attempts, averaging all attempt rows weights students with more attempts more heavily. The database does not infer that you intended one result per student.

12 | WHERE CHANGES THE VALUES ENTERING THE CALCULATION

SELECT ClassGroup, COUNT(Score) AS RecordedScoreCount,
       AVG(Score) AS MeanScore
FROM Assessment
WHERE Score >= 60
GROUP BY ClassGroup
ORDER BY ClassGroup ASC;
ClassGroupRecordedScoreCountMeanScore
A270
B380

WHERE Score >= 60 removes rows before grouping. Aisha’s NULL does not satisfy the comparison, and Mei’s zero fails it. The surviving Class A scores are 80 and 60; the surviving Class B scores are 90, 70 and 80.

Class B’s mean is now 80 because the zero-score row was excluded. This is the mean of scores at least 60, not the mean of the whole class. The report label must make that restriction clear.

Filtering can be legitimate, but it changes the population being summarised. A technically correct query can still support a misleading conclusion if the reader thinks it includes every student.

13 | HAVING CHOOSES WHICH GROUPS TO REPORT

SELECT ClassGroup, COUNT(Score) AS RecordedScoreCount,
       AVG(Score) AS MeanScore
FROM Assessment
WHERE Score >= 60
GROUP BY ClassGroup
HAVING AVG(Score) >= 75
ORDER BY MeanScore DESC, ClassGroup ASC;
ClassGroupRecordedScoreCountMeanScore
B380

First WHERE keeps scores of at least 60. Then GROUP BY forms the class groups and AVG calculates their means. HAVING AVG(Score) >= 75 retains B’s 80 and rejects A’s 70.

WHERE applies to individual rows; HAVING can apply to a calculated group result. At this query level, WHERE AVG(Score) >= 75 is wrong because the grouped mean is not available at the row-filtering stage.

For portability, repeat AVG(Score) in HAVING rather than assuming its SELECT alias MeanScore can be referenced there. ORDER BY commonly accepts that output alias. Written clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

14 | EMPTY INPUT AND ALL-NULL INPUT ARE DIFFERENT

SELECT COUNT(*) AS RecordCount, COUNT(Score) AS RecordedScoreCount,
       SUM(Score) AS TotalScore, AVG(Score) AS MeanScore,
       MIN(Score) AS LowestScore, MAX(Score) AS HighestScore
FROM Assessment
WHERE Score > 100;
RecordCountRecordedScoreCountTotalScoreMeanScoreLowestScoreHighestScore
00NULLNULLNULLNULL

No score is over 100 in this dataset. For this overall aggregate without GROUP BY, SQL returns one summary row: both counts are zero, while SUM, AVG, MIN and MAX have NULL results. SUM of no input values is not automatically zero.

Now consider the existing row whose score is missing:

SELECT COUNT(*) AS RecordCount, COUNT(Score) AS RecordedScoreCount,
       SUM(Score) AS TotalScore, AVG(Score) AS MeanScore
FROM Assessment
WHERE Score IS NULL;
RecordCountRecordedScoreCountTotalScoreMeanScore
10NULLNULL

There is one row but no known score, so COUNT(*) distinguishes this from the empty case. If the grouped query has no input rows, no class groups are formed and it returns zero rows, rather than an invented class summary.

A report may deliberately substitute a display value using COALESCE, but that does not establish a missing score as a real zero. Choose the interpretation before replacing NULL.

15 | ROUND FOR PRESENTATION, NOT TO CHANGE THE DATA

SELECT ClassGroup, ROUND(AVG(Score), 2) AS MeanScore
FROM Assessment
GROUP BY ClassGroup
ORDER BY ClassGroup;

ROUND(AVG(Score), 2) rounds the resulting mean to two decimal places in this example’s SQL dialect. It is not the same as rounding each input score first.

The alias describes a result column; the original scores remain unchanged. Formatting software may display 70, 70.0 or 70.00 even when the numeric value is equivalent.

If a threshold is intended to apply to the unrounded mean, compare AVG(Score) in HAVING rather than a rounded display value. Otherwise a value just below a threshold might be included after rounding. Check numeric types and ROUND syntax in your DBMS.

16 | DO NOT AVERAGE AVERAGES WITHOUT THEIR WEIGHTS

Class A’s known-score mean is 70 based on two scores. Class B’s is 60 based on four. The simple average of the two class means is (70 + 60) ÷ 2 = 65, but the overall known-score mean is 380 ÷ 6 = 63.333….

The means differ because equal weight was given to classes of different known-score counts. To recover the pooled mean, use (70 × 2 + 60 × 4) ÷ (2 + 4), or calculate AVG directly from the intended underlying score rows.

Neither statistic is automatically meaningless: equal weighting of classes answers a different question from equal weighting of recorded scores. State the population and weighting that your decision needs.

17 | AGGREGATES AFTER A JOIN NEED THE RIGHT GRAIN

If Assessment is joined to a table containing three feedback comments for Maya’s result, her score may appear three times in the joined rows. AVG would then weight her score three times; SUM and COUNT could also be inflated.

Before calculating, identify the grain: what one input row represents. Check whether the join preserves one row per assessment or multiplies it. Aggregate at the intended level or restructure the query appropriately.

AVG(DISTINCT Score) is not a general repair: Maya and Ivo legitimately both scored 80, and collapsing equal score values changes the statistic. COUNT(DISTINCT AssessmentID) can count unique assessments, but it does not automatically repair every sum or mean.

18 | CONSTRUCT A REPORT THAT SUPPORTS A DECISION

Request: For each class with at least three recorded scores, return the class, recorded-score count, lowest score, highest score and mean. Show the highest mean first.

The threshold concerns the number of known scores, so use COUNT(Score), not COUNT(*). Group by class, then filter the group count with HAVING. There is no row-level score threshold in this request.

SELECT ClassGroup, COUNT(Score) AS RecordedScoreCount,
       MIN(Score) AS LowestScore, MAX(Score) AS HighestScore,
       AVG(Score) AS MeanScore
FROM Assessment
GROUP BY ClassGroup
HAVING COUNT(Score) >= 3
ORDER BY MeanScore DESC, ClassGroup ASC;
ClassGroupRecordedScoreCountLowestScoreHighestScoreMeanScore
B409060

Class A has three records but only two recorded scores, so it does not meet the condition. Class B does. A mean of 60 alongside a range from 0 to 90 suggests varied performance; the mean alone hides that spread.

The report summarises recorded evidence. It does not prove why students performed differently or justify treating missing results as zero. Use it to identify questions for further investigation.

19 | CHOOSE THE RIGHT CALCULATION

Choose the best function or clause for each requirement. Your first checked answer counts toward the score; restart to practise again.

20 | CHECK THE MEANING BEFORE THE SYNTAX

Ask what is counted, what is summed and which values enter the denominator. Distinguish rows from non-null values, repeated real observations from duplicate output and zero from missing data.

For a grouped report, identify the grouping key, any row filter and any group threshold. Ensure each displayed non-aggregate belongs to the grouping, then specify the required ordering.

Finally test a known zero, a missing value, repeated equal scores, an empty scope and unequal group sizes. These examples reveal mistakes that a set of positive, complete, different values can hide.

Reference: SQL aggregate function definitions.

MATCH THE AGGREGATE TO ITS PURPOSE

Terminology

Terminology

Aggregate function

A function producing a summary value from a set of input rows or values.

AVG

SQL function calculating the arithmetic mean of non-null input values.

AVERAGE

The mean operation named in the syllabus; executable SQL normally spells the function AVG.

COUNT(*)

Counts all selected rows, including rows with NULL fields.

COUNT(column)

Counts rows with a non-null value for the specified expression.

COUNT(DISTINCT column)

Counts different non-null values of the specified expression.

SUM

Adds the non-null input values.

MIN

Returns the smallest non-null input value for a supported ordered type.

MAX

Returns the largest non-null input value for a supported ordered type.

Arithmetic mean

Total of the included numeric values divided by their count.

NULL

An absent or unknown value, not a known zero.

Zero

A known numeric value included by numeric aggregates and COUNT(column).

GROUP BY

Forms groups of rows sharing the specified grouping values.

Grouping key

The expression or combination defining membership of a group.

WHERE

Filters individual rows before grouping in the query’s logical meaning.

HAVING

Filters groups, often using an aggregate condition.

Alias

A temporary name for an output expression or table reference.

Result set

The returned query rows and columns, distinct from modifying stored source data.

ORDER BY

Specifies the returned order of report rows.

ASC

Ascending order.

DESC

Descending order.

ROUND

Rounds a numeric result to a specified precision under the DBMS’s rules.

Decimal type

A numeric type suitable for specified decimal precision and scale, subject to DBMS behaviour.

Empty input

No rows satisfy the input scope or filter.

All-null input

Input rows exist but the aggregated expression has no known values.

COALESCE

Returns the first non-null expression, sometimes used for deliberate display substitutions.

Weighted mean

A mean accounting for differing observation weights or group sizes.

Grain

What one row of the calculation’s input represents.

Join multiplication

A join producing multiple input rows for a previously single observation.

Report population

The rows or observations a report is intended to summarise.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe the full dataset?
2. Which are correct overall calculations?
3. Which describe extrema?
4. Which describe DISTINCT?
5. Which are correct class summaries?
6. Which describe WHERE and HAVING?
7. Which hold for an overall aggregate with no input rows?
8. Which distinguish all-null input?
9. Which are good construction practices?
10. Which prevent misleading summaries?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Construct a query returning total records and recorded scores.

2. Construct a query returning total, mean, minimum and maximum score.

3. Explain why the mean uses a denominator of six.

4. Construct a query returning the number of different known score values.

5. Construct a count and mean report per class.

6. Construct a class-mean report using only scores of at least 60.

7. Construct a query retaining only groups with a mean of at least 75 after the sixty-point row filter.

8. Construct a report for classes with at least three known scores.

9. Explain the difference between empty input and all-null score input.

10. Explain why the simple average of class means differs from the overall score mean.

11. Explain why a join can distort an aggregate.

12. Construct a query retrieving students whose score equals the maximum.

CONSTRUCT IT | A CLASS REPORT

Construct an overall report containing record count, recorded-score count, total, mean, minimum and maximum. Then construct a report per class, retaining only groups with at least three known scores and ordering by mean descending. Explain the different denominators and why missing scores are not zero. Extend the report with a row filter and state how that changes its population.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.3.4 SQL Aggregate Functions (HL ONLY) is coming soon. Complete the matching task and written questions in your exercise book.