Perform Calculations (CIE IGCSE ICT)

Use of Arithmetic Operations or Numeric Functions

• In a database, you can use arithmetic operations or numeric functions to perform calculations
• Calculated Fields are fields that carry out a calculation based on other number fields in the database
  • Let's say you have a products table with Price and Quantity fields. You could create a TotalCost a calculated field like this:
  • TotalCost = Price * Quantity
  • This calculation multiplies the price of each item by its quantity to find the total cost
• Calculated Controls are objects you place on forms or reports to display the result of an expression
  • You might have a form in a sales database where you input the QuantitySold and UnitPrice. A calculated control could be used to display the TotalSale:
  • TotalSale = QuantitySold * UnitPrice
  • This displays the total sale on the form without storing it in the database

Using Formulae and Functions to Perform Calculations

• Databases allow you to use formulae and functions to perform calculations at run time
• This can include basic arithmetic operations: addition, subtraction, multiplication, and division
• Suppose you have a discount field and you want to subtract it from the total cost, you could use a subtraction operation like this:
  • FinalCost = TotalCost - Discount

Aggregate Functions

You can also use aggregate functions to calculate statistical information about a set of records. Some examples include:

• Sum - Adds together all the numbers in a column
  • To find the total cost of all products sold, you could use the SUM function on the TotalCost field:
  • SUM(TotalCost)
• Average - Computes the average of a set of numbers in a column
  • To find the average price of all products, you could use the AVERAGE function:
  • AVERAGE(Price)
• Maximum - Finds the highest number in a column
  • To find the most expensive product, you could use the MAX function on the Price field:
  • MAX(Price)
• Minimum - Finds the lowest number in a column
  • To find the least expensive product, you could use the MIN function:
  • MIN(Price)
• Count - Counts the number of rows in a column
  • To find the number of products in the database, you could use the COUNT function:
  • COUNT(ProductID)

Remember that the actual syntax and function names might differ slightly depending on the specific database system being used.