databasedev.co.uk - database solutions and downloads for 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.

SecA_Bal

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:-

SELECT SUM(Balance) AS TOTAL
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
FROM SecA_Bal
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:

SELECT COUNT (*)
FROM SecA_Bal
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:-

SELECT MAX(Balance)

FROM SecA_Bal
WHERE Customer# > 1999
AND AGENT=Dowling

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.