- database solutions and downloads for microsoft access

SQL Aggregate Functions in Microsoft Access

Aggregate Functions of SQL For Beginners:

The data manipulation side of SQL (Structured Query Language) allows for various types of queries. Very often it will be necessary to retrieve summarized information from the data contained in the database tables, and it is then we turn to the SQL Aggregate Functions.

When we need the sum or the average of a set of values for instance. or when we wish to know how many records match given conditions, or which is the lowest or highest value in a particular set or subset of data.


Name Customer# Agent Balance
John Smith 2593 Brown 121.50
Neale Fenn 2529 Brown -42.00
Kenny Perry 2511 Dowling 553.50
Mark Yeates 1501 Dowling 383.81

Statements like SUM, AVG, and MIN/MAX help us manipulate data in the database to perform queries where the answer does not already exist as a value in the database.

SQL Sum Statement

The SUM statement is used exactly as you might expect, to sum related values. It is used as a sub-function of the SELECT statement as follows:-

FROM SecA_Bal;

This will return a TOTAL of 1100.81as the outstanding balance of all customers in SecA_Bal.

SQL AVG (Average) Statement

The AVG command is again used with the Select statement, and delivers the average value of a range of integers. The command is used thus:-

SELECT AVG(Balance) As Avg_Balance
WHERE Balance >0;

This will deliver a result called Avg_Balance which will be the average amount owed by customers, ignoring customers whose accounts are in debit.

The SQL COUNT Function

COUNT(*) returns the number of rows satisfying given conditions.

For example:

WHERE Balance >0;

This Query will return the answer 3, the number of rows which have a balance greater than 0, or the number of customers in SecA_Bal owing money.

SQL MIN/MAX Statements

Finding the minimum or maximum value in a set of data can also be extremely useful. This can be achieved with the MIN/MAX commands as follows:-


WHERE Customer# > 1999

This query will tell us which is the highest balance any customer owes agent Dowling.

The Aggregate functions can be combined, and used in conjunction with each other and with other commands.

For example:

SELECT SUM(Balance), AVG(Balance)
FROM SecA_Bal;

The Aggregate Functions are essential to SQL as they greatly increase the scope of the language in terms of data manipulation, and it’s capability in terms of serving disparate users needs.

Aggregate functions are typically used in summarising data and generating reports, and thus add value to data for management and other users not directly working with the database.