Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

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.

Make

Model

Type

——–

——–

—–

Nokia

N3200

GSM

Samsung

R220

GSM

Motorola

C210

GSM

Nokia

N6112

CDMA

Motorola

C110

GSM

Samsung

BOSS

CDMA

Samsung

X600

3G

Motorola

V300

3G

Nokia

N70

3G

Nokia

N9300

3G

Nokia

N600

GSM

Motorola

P280

GSM

Motorola

RAZR

CDMA

Samsung

SCH191

CDMA

Samsung

SCH N380

CDMA

Nokia

N6255

CDMA

Nokia

N8800

GSM

LG

RD2230

CDMA

Samsung

C100

GSM

LG

RD3130

CDMA

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

Continues…

Leave a comment

Your email address will not be published.