Aggregate Functions: COUNT, SUM, and AVG Explained

The SUM function returns the total of numeric values in a field.

SUM is used when:

• Values represent quantities
• Totals are required

Examples include:

• Total sales
• Total marks
• Total hours worked

Key IB point:

• SUM only works on numeric fields
• Text fields cannot be summed

SUM is commonly combined with WHERE to calculate totals for specific conditions.

AVG Explained

The AVG function returns the average (mean) value of a numeric field.

AVG is used to:

• Find typical values
• Compare performance
• Analyse trends

For example:

• Average score
• Average salary
• Average transaction value

IB students should understand that:

• AVG adds values
• Then divides by the number of records

It produces one value, not a list.

How Aggregate Functions Change Query Results

A key IB idea is that aggregate functions:

• Collapse many records into one result

This means:

• You no longer see individual rows
• You see a summary value

Understanding this conceptual change is essential for exam explanations.

Aggregate Functions and WHERE

Aggregate functions are often used with WHERE to:

• Limit which records are included in the calculation

For example:

• Count only records from a certain category
• Calculate averages for a specific group

Students should explain that:

• WHERE filters first
• The aggregate function is applied after

GROUP BY (Conceptual Link)

While GROUP BY is covered separately, IB students should recognise that:

• Aggregate functions are often used with GROUP BY
• GROUP BY allows calculations per category

This prepares students for more advanced database questions.

Common Student Mistakes

Students often:

• Expect aggregate functions to return multiple rows
• Forget they return a single value
• Use aggregate functions on non-numeric fields
• Give vague explanations of results

Clear descriptions of input vs output score higher.

How Aggregate Functions Appear in IB Exams

IB questions may ask students to:

• Choose the correct aggregate function
• Explain what a query returns
• Interpret summary results
• Justify why aggregation is useful

Understanding purpose is more important than syntax.

Final Thoughts

Aggregate functions such as COUNT, SUM, and AVG allow databases to turn large amounts of data into meaningful summaries. Instead of listing every record, they provide totals, averages, and counts that support decision-making.

Understanding how aggregate functions work gives IB Computer Science students the ability to explain data analysis clearly and confidently — exactly what examiners expect.

Learn the differences between HDDs, SSDs, and NAS for IB Computer Science, including performance, cost, and use cases.