SQL 2005+

SQL Server Basic Aggregation Functions (2)

The twenty-seventh part of the SQL Server Programming Fundamentals tutorial describes the use of the aggregation functions that can be used when executing queries. These functions allow you to perform calculations based upon multiple result rows.

Filtering Queries

So far, the queries we have executed have counted the rows from an entire table. It is possible to include a WHERE clause in a query to filter the results before performing a calculation. In the next example, only addresses within the city of Leeds are counted. This should return a value of two.

SELECT count(*) FROM CustomerAddresses WHERE TownOrCity='Leeds'

Summing Columns

The second aggregate function that we will consider is sum. This function can be applied to numeric columns to add all of the non-null values together and return the total. The syntax is similar to that of the count function but you may not use an asterisk between the parentheses.

To obtain the total value for all of the contracts in the JoBS database, we can use the following T-SQL statement:

SELECT sum(ContractValue) FROM Contracts

The value being totalled need not be a simple column name. You may include a mathematical expression to be calculated for each row in the table before the summing occurs. A very simplistic expression could double the contract value before totalling.

NB: This query is for example purposes only. In reality it would be more efficient to calculate the sum and double the final result.

SELECT sum(ContractValue * 2) FROM Contracts

As will the count function, you may decide to only sum distinct results using the DISTINCT clause.

SELECT sum(DISTINCT ContractValue) FROM Contracts

Calculating Averages

The avg function calculates the arithmetic mean of a column or expression. This is achieved by summing all of the values and dividing by the number of rows. However, as with all other aggregate functions, any NULL values are ignored. This is important when calculating the average as it can produce unexpected results. For example, if the column being processed contains the values 1, 2, 3, 4 and NULL the sum of the values is 10. You may expect that the average would be 2 as there are five rows. Actually, the result of the avg function will be 2.5 as the total is divided by the number of non-null rows (4), not the overall number of rows (5).

To calculate the average contract value for all contracts, run the following query:

SELECT avg(ContractValue) FROM Contracts

NB: As with the sum function, you can use the DISTINCT clause.

Maximum and Minimum Values

The last two aggregate functions that we will consider are min and max. These return the smallest and largest values respectively when used with numeric columns. These functions may also be applied to character-based columns. Used in this manner, they return the first or last item in the column according to the sort sequence of the table.

SELECT max(ContractValue), min(ContractValue) FROM Contracts