Be able to use formulae and functions to perform calculations at run time including addition, subtraction, multiplication, division, sum, average, maximum, minimum, count

Published by Patrick Mutisya · 14 days ago

Cambridge IGCSE ICT 0417 – Topic 18: Databases

Topic 18 – Databases

Objective

Be able to use formulae and functions to perform calculations at run time, including:

  • Addition

  • Subtraction

  • Multiplication

  • Division

  • SUM

  • A \cdot ERAGE

  • MAXIMUM

  • MINIMUM

  • COUNT

1. Why perform calculations in a database?

Calculations at run time allow users to:

  • View up‑to‑date totals without manual re‑entry.

  • Analyse trends and make decisions quickly.

  • Reduce errors caused by copying data between tables.

2. Formula fields (calculated fields)

A formula field stores a calculation that is performed each time a record is displayed or updated. The result is not stored permanently; it is generated on the fly.

Typical syntax (varies by DBMS):

FieldName: Expression

Example using arithmetic operators:

\$\text{TotalPrice} = \text{Quantity} \times \text{UnitPrice}\$

3. Common aggregate functions

Aggregate functions operate on a set of records and return a single value.

FunctionPurposeSyntax exampleResult (example data)
SUM()Adds all values in a numeric field.SUM(Quantity)150 (if quantities are 30, 45, 75)
A \cdot G()Calculates the average of a numeric field.A \cdot G(UnitPrice)12.5
MAX()Finds the highest value in a field.MAX(TotalPrice)375
MIN()Finds the lowest value in a field.MIN(TotalPrice)90
COUNT()Counts the number of records (or non‑null entries).COUNT(*)5 (five records)

4. Example: Sales table

Consider a simple sales table that records each transaction.

TransactionIDItemQuantityUnitPrice ($)Total ($)
1Notebook102.50\$10 \times 2.50 = 25.00\$
2Pen250.80\$25 \times 0.80 = 20.00\$
3Folder53.20\$5 \times 3.20 = 16.00\$
4Marker121.50\$12 \times 1.50 = 18.00\$
5Stapler37.00\$3 \times 7.00 = 21.00\$

5. Using functions on the Sales table

  1. Total sales value: SUM(Total)

    \$\text{Total Sales} = \sum{i=1}^{5} \text{Total}i = 25 + 20 + 16 + 18 + 21 = 100\$

  2. Average unit price: A \cdot G(UnitPrice)

    \$\text{Average Unit Price} = \frac{2.50 + 0.80 + 3.20 + 1.50 + 7.00}{5} = 3.00\$

  3. Highest transaction total: MAX(Total)

    \$\text{Maximum Total} = 25\$

  4. Lowest transaction total: MIN(Total)

    \$\text{Minimum Total} = 16\$

  5. Number of transactions: COUNT(TransactionID)

    \$\text{Count} = 5\$

6. Combining arithmetic with functions

It is possible to embed arithmetic inside an aggregate function. Example:

SUM(Quantity * UnitPrice) – adds the product of quantity and unit price for each record, giving the same result as SUM(Total).

\$\sum{i=1}^{5} (\text{Quantity}i \times \text{UnitPrice}_i) = 100\$

7. Common pitfalls

  • Division by zero – always check that the divisor is not zero before using /.

  • Mixing data types – ensure fields used in arithmetic are numeric.

  • NULL values – most functions ignore NULL, but arithmetic with NULL returns NULL.

8. Quick reference cheat‑sheet

OperationFormula / FunctionExample
AdditionFieldA + FieldB\$5 + 3 = 8\$
SubtractionFieldA - FieldB\$10 - 4 = 6\$
MultiplicationFieldA * FieldB\$7 \times 2 = 14\$
DivisionFieldA / FieldB\$20 \div 5 = 4\$
SumSUM(Field)\$\sum 1..n = 100\$
AverageA \cdot G(Field)\$\frac{\sum}{n} = 12.5\$
MaximumMAX(Field)\$\max = 75\$
MinimumMIN(Field)\$\min = 3\$
CountCOUNT(Field)\$\text{records} = 5\$

Suggested diagram: Flow of a calculated field – user enters Quantity and UnitPrice, database computes Total using the formula Total = Quantity × UnitPrice, then aggregate functions (SUM, A \cdot G, etc.) are applied to the Total column.