Learning Objectives
- Use SUM to total numeric field values.
- Use COUNT to count records or values as shown in a query.
- Apply WHERE conditions before an aggregate is calculated.
- Identify the single aggregate result produced by a query.
Key Terms
- Aggregate function
- A function that calculates one result from several records.
- SUM
- Returns the total of numeric values in a selected field.
- COUNT
- Returns the number of records or values included by the query.
- Aggregate result
- The single calculated value returned by an aggregate query.
- Filtered aggregate
- An aggregate calculated only from records satisfying WHERE.
- Numeric field
- An integer or real field that can be totalled.
Purpose Of Aggregates
SUM and COUNT produce a calculated result rather than listing one output row for every record. They are the only aggregate functions required in Topic 9.
SUM adds numeric values from a field. COUNT finds how many records or values are included by the query. Both may be combined with WHERE so the calculation uses only matching records.
Do not introduce AVG, MIN, MAX or GROUP BY because they are not listed in the syllabus.
Using SUM
This query adds every TotalCharge value in the Bookings table and returns one total. The field must be numeric, normally integer or real.
SUM would not be meaningful for CustomerName or BookingRef because those fields are text identifiers.
The query returns a calculated value, not the individual booking records.
SELECT SUM(TotalCharge)
FROM Bookings;Filtered SUM
The WHERE condition first selects bookings for which payment has been received. SUM then adds TotalCharge only for those matching records.
A booking with PaymentReceived = FALSE is not included in the total even if it has a positive charge.
SELECT SUM(TotalCharge)
FROM Bookings
WHERE PaymentReceived = TRUE;Using COUNT
This query counts the StudentID values in records where YearGroup is 10. If StudentID is the primary key, every matching record has a value, so the result is the number of matching students.
The exact COUNT syntax may be partially supplied in an examination question. Complete it using the shown field and structure rather than adding variants that are not requested.
COUNT returns an integer.
SELECT COUNT(StudentID)
FROM Students
WHERE YearGroup = 10;SUM Versus COUNT
| Requirement | Function | Reason |
|---|---|---|
| Total value of all sales | SUM(SaleValue) | Adds numeric amounts |
| Number of paid bookings | COUNT(BookingRef) with WHERE PaymentReceived = TRUE | Counts matching records |
| Total participants in all bookings | SUM(ParticipantCount) | Adds the counts stored in records |
| Number of booking records | COUNT(BookingRef) | Counts records using a present identifier |
Worked Data
| BookingRef | ParticipantCount | TotalCharge | PaymentReceived |
|---|---|---|---|
| B01 | 2 | 40.00 | TRUE |
| B02 | 4 | 72.00 | FALSE |
| B03 | 1 | 25.00 | TRUE |
| B04 | 3 | 54.00 | TRUE |
Calculating Results
SELECT SUM(TotalCharge) FROM Bookings; returns 191.00 because 40 + 72 + 25 + 54 = 191.
SELECT SUM(TotalCharge) FROM Bookings WHERE PaymentReceived = TRUE; returns 119.00 because B01, B03 and B04 match.
SELECT COUNT(BookingRef) FROM Bookings WHERE PaymentReceived = TRUE; returns 3. SELECT SUM(ParticipantCount) FROM Bookings; returns 10.
Interpreting The Requirement
The word total can be ambiguous. Total money or total participants normally requires SUM because stored numeric values are added. Total number of records normally requires COUNT.
Ask whether the required answer is the addition of values or the number of qualifying records. This distinction is frequently assessed.
Worked Examples
Counting Available Products
Question: Write a query to count products where IsAvailable = TRUE, using ProductID as the counted field.
- Use COUNT because the requirement is the number of records.
- Count a field present in every matching record.
- Use WHERE for the Boolean condition.
Answer: SELECT COUNT(ProductID) FROM Products WHERE IsAvailable = TRUE;
Total Stock
Question: Write a query to calculate the total StockQuantity for products in Category A.
- Use SUM because quantities must be added.
- Apply SUM to StockQuantity.
- Filter Category = “A”.
Answer: SELECT SUM(StockQuantity) FROM Products WHERE Category = “A”;
Examination Guidance
- Decide whether to add values or count records.
- Use SUM only with numeric fields.
- Apply WHERE before calculating a filtered aggregate.
- Expect one calculated result.
- Stay within SUM and COUNT only.
Common Mistakes
- Using COUNT when numeric values should be added.
- Using SUM to count records.
- Applying SUM to text.
- Including non-matching records in a filtered calculation.
- Introducing AVG or GROUP BY outside the syllabus.
Knowledge Check
1. What does SUM return?
2. What does COUNT return?
3. What type does COUNT return?
4. What happens before a filtered aggregate?
5. Which function gives total participant numbers stored in records?
