1. Computing
Aggregate Functions in SQL
Part 1: Introducing SUM and AVG
More of this Feature
Part 2: Counting Records
Part 3: Max and Min
Join the Discussion
"Need some assistance or advice? Join us in the About Databases forum."
Mike Chapple

By their very nature, our databases contain a lot of data. In previous features, we've explored methods of extracting the specific data we're looking for using the Structured Query Language (SQL). Those methods worked great when we were seeking the proverbial needle in the haystack. We were able to answer obscure questions like "What are the last names of all customers who have purchased Siberian wool during the slow months of July and August?"

Oftentimes, we're also interested in summarizing our data to determine trends or produce top-level reports. For example, the purchasing manager may not be interested in a listing of all widget sales, but may simply want to know the number of widgets sold this month. Fortunately, SQL provides aggregate functions to assist with the summarization of large volumes of data. In this three-segment article, we'll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.

All of our queries will use the WidgetOrder table described below. Please note that this table is not normalized and I've combined several data entities into one table for the purpose of simplifying this scenario. A good relational design would likely have Products, Orders, and Customers tables at a minimum.

OrderID

FirstName

LastName

Quantity

UnitPrice

Continent

122

John

Jacob

21

4.52

North America

923

Ralph

Wiggum

192

3.99

North America

238

Ryan

Johnson

87

4.49

Africa

829

Mary

Smith

842

2.99

North America

824

Elizabeth

Marks

48

3.48

Africa

753

James

Linea

9

7.85

North America

942

Alan

Jonas

638

3.29

Europe

Let's begin by taking a look at the SUM function. It is used within a SELECT statement and, predictably, returns the summation of a series of values. If the widget project manager wanted to know the total number of widgets sold to date, we could use the following query:

SELECT SUM(Quantity) AS Total
FROM WidgetOrders

Our results would appear as:

Total
-----------
1837

The AVG (average) function works in a similar manner to provide the mathematical average of a series of values. Let's try a slightly more complicated task this time. We'd like to find out the average dollar amount of all orders placed on the North American continent. Note that we'll have to multiply the Quantity column by the UnitPrice column to compute the dollar amount of each order. Here's what our query will look like:

SELECT AVG(UnitPrice * Quantity) As AveragePrice
FROM WidgetOrders
WHERE Continent = "North America"

And the results:

AveragePrice
---------------------
862.3075

In the next section of this article, we'll explore methods used for counting the number of records that meet given criteria. Read on!

Next page > Counting Records > Page 1, 2, 3

Discuss in my forum

©2014 About.com. All rights reserved.