SQL Aggregate Functions in Access

Overview

This lecture covers how to use the COUNT, SUM, AVG, MIN, and MAX functions in Microsoft Access SQL to analyze the "ticket price" field in a table.

Basic SQL Query Structure

• SQL queries in Access commonly start with the SELECT statement.
• Reserved SQL keywords (e.g., SELECT, FROM) are often written in uppercase for clarity.
• Queries can be written in SQL view and don't require showing tables in Query Design.

Using Aggregate Functions

• The COUNT function calculates the number of records in a column.
• COUNT(ticket price) returns the number of ticket price entries (e.g., 150 passengers).
• The SUM function adds up all values in the specified column (total ticket price amount).
• The AVG function calculates the average value in a column (average ticket price).
• The MAX function returns the highest value in a column (maximum ticket price).
• The MIN function returns the lowest value in a column (minimum ticket price).
• Multiple aggregate functions can be included in a single SELECT statement, separated by commas.

Customizing Column Headings

• If no column heading is specified, Access uses a default like EXPR1.
• Use the AS statement to rename query result columns (e.g., AS CountOfPassengers).
• Enclose column names with spaces in square brackets (e.g., AS [Count of Passengers]).

Example Output Results

• COUNT returns number of passengers: 150.
• SUM returns total ticket prices: 147,547.
• AVG returns average ticket price: $983.65.
• MAX returns highest ticket price: $9,000.
• MIN returns lowest ticket price: $99.

Key Terms & Definitions

• COUNT — Returns the number of records in a specified column.
• SUM — Calculates the total sum of a numeric column.
• AVG — Returns the average value of a numeric column.
• MAX — Finds the highest value in a column.
• MIN — Finds the lowest value in a column.
• AS — Used to assign a custom name to a result column in SQL.

Action Items / Next Steps

• Practice writing SQL queries using COUNT, SUM, AVG, MIN, and MAX functions in Access.
• Prepare for the next lesson on sorting and grouping data.