Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

By : Brad McGehee Feb 03, 2006

Printer friendly

Aggregate functions, a staple means of summarizing large volumes of data for serious database developers, are mainly used for generating business data reports. Aggregate functions allow you to retrieve summarized information from a table by operating on a set of data as a whole rather than on each data element. These functions contribute to keeping unnecessary load and coding out of the display pages. This article will acquaint you with aggregate functions such as MIN, MAX, COUNT, and AVG, which easily let you perform tasks that you may have thought needed extensive programming codes to accomplish. Besides returning a single summarized value, aggregate functions can also perform common DBA tasks such as deleting duplicate records.

The Need for Aggregate Functions

"Aggregate," in the context of a database, is a collection or subset of records. The grouping of data is entirely up to the DBA. You can either choose a large group consisting of all the records or a small one with just a few records. Clubbing data based on the attributes of a particular group such as age, gender, or department is a traditional approach to grouping. To group the data for easy statistical calculation (average, totals), you use the ORDER BY keyword.

For example, to calculate the average salary of people in a particular department, you can group the salaries of all the workers in that department and use the aggregate functions. In earlier versions of SQL Server, nested query procedures were used to perform such calculations. Although a nested query can fetch the same result as the aggregate functions, the latter is preferred because writing queries is a complex task and consumes more time.

Consider the following list of mobile phones.

If you wanted to select a mobile phone of type "GSM," you could write the following query.

SELECT model FROM Mobile_phones WHERE make =
(SELECT make FROM Mobile_phones WHERE type = 'GSM')

In case you select the type "CDMA" or "3G," you will need to run the above query twice again to get the result. The other option of course is to use aggregate functions, for which you do not need to write separate queries.

Butwhat if you wanted to get a list of the total number of mobile phones by type? Would you use just one query, or break it into different loops and queries?

The answer is just one query. A single query including the aggregate function COUNT() with the GROUP BY clause will get the result you want.

SELECT Type, COUNT (type) AS Tot_Num
FROM Mobiles_phones GROUP BY Type