SQL's ability to make sense of large sets of data by using simple functions that allow you to aggregate data is a major tool that every programmer should master. Learn how to make the most of SQL's aggregate functions.

How do you calculate a total amount based on detail records in RPG? You probably read each record, add the respective amount to a work variable, and move on to the next record, until the file (or subset of records) ends. Well, in SQL you can do the same with a single instruction. Let's explore the awesome power and flexibility of SQL's aggregate functions, making use of what I explained before about the Select SQL instruction. In that article, I introduced an Inventory Master Table, which I have been using in most examples. This table's name is InvMst, and its description is presented in the table below:

Let's see, for instance, how to return the total quantity of the Item with ID 'A123' that exists in warehouse 24. SQL provides several types of functions you can use—together with constants, operators, and column names—to create all sorts of expressions. In this case, I'll use a SUM function, which does the same as its MS Excel counterpart: returns the sum of a set of values. Here's how you use it to get the total quantity of Item 'A123' that exists in warehouse 24:

SELECT      ItemID

            , SUM(ItemQty)

FROM        InvMst

WHERE       WHID = 24

GROUP BY    ItemID

Notice that last line, GROUP BY ItemID? This is a requirement of some functions, even though you can use it whenever you want to group results, regardless of the functions you specified in the Select clause. The Group By reserved expression does what its name implies (allows you to group the results by one or more columns), and its structure is similar to Order By (it also expects one or more column names). It will aggregate the records retrieved that have those columns' contents in common. In this simple example, I'm grouping by ItemID and getting a single line as a result, but I could add a comma and "ShelfID" to the Group By expression, and the aggregation would be different: I'd get a line per each ItemID / ShelfID combination.

In abstract terms, an aggregate function, such as the SUM, takes a set of values (like a column of data) and returns a single value result from the set of values. This set of values will be determined by the WHERE and GROUP BY clauses of your SQL statement.

One of the most commonly used is the average function. Calculating an average in RPG is not very difficult, but it requires a loop and at least two variables; you read your data in a loop, sum up the values into a variable, and increase a counter (your second variable). In the end, you just divide the sum by the counter and voilá! The average is calculated.

In SQL, however, there's a function for that: AVG. Its syntax is similar to SUM; you just need to specify the field name for which you want to calculate the average enclosed in parentheses after the function name: AVG(<field name>). You can use it in all DML statements, even though I can't imagine how this can be used in a Delete statement. Anyway, here's an example: calculating the average quantity of item 'A123' per shelf in warehouse 333 is as simple as this:

SELECT            ItemID

            , AVG(ItemQty)

FROM        InvMst

WHERE       ItemID = 'A123'

            AND WHID = 333

GROUP BY    ItemID, ShelfID

I'm grouping by ShelfID to guarantee that my average is per shelf. If I hadn't specified the GROUP BY clause, the average would be calculated for the warehouse 333. AVG has two optional and mutually exclusive keywords, ALL and DISTINCT, which you can use to include all values or disregard the duplicates, respectively. By default, all values are considered. In other words, specifying ALL is redundant. However, there may be times in which it makes sense to use DISTINCT. Here's the average calculation from the previous example, disregarding the duplicate item quantities:

SELECT            ItemID

            , AVG(DISTINCT ItemQty)

FROM        InvMst

WHERE       ItemID = 'A123'

            AND WHID = 333

GROUP BY    ItemID, ShelfID

When you run this statement, it will return a rather ugly AVG column value. Assuming that the average is one hundred, the returned value is 100.000000000000000000000000, because of the way the average is internally calculated by the database engine. You have a few different ways to "beautify" the output, similar to RPG's %EDITC and %EDITW, but let's take the opportunity to introduce another useful SQL instruction: CAST. SQL can work magic, but it doesn't cast spells; it casts data types. CAST's syntax is simple:

CAST ( <expression> as <data type>).

The following example transforms the average column into a DECIMAL (9, 2) column:

SELECT            ItemID

, CAST(AVG(DISTINCT ItemQty) AS dec (9,2))

FROM        InvMst

WHERE       ItemID = 'A123'

AND WHID = 24

GROUP BY   ItemId, ShelfId

I'm highlighting the changes in bold. You can use CAST in all the places you'd use a column or expression. However, you need to use it carefully, because some CAST operations require some processing, which will cause the operation to take longer.

If I wanted to count the shelves that contain the 'A123' item instead of calculating their average quantity, SQL also provides a simple to use function for that: COUNT. The syntax is similar to AVG in every way, right down to the ALL and DISTINCT keywords. The difference is, other than the obvious operation that is executed, you can use COUNT for any type of expression, while AVG only works with numeric expressions or fields. Here's a simple count scenario: I want to count how many shelves in warehouse 333 contain item 'A123':

SELECT            ItemID

            , COUNT(ShelfID)

FROM        InvMst

WHERE       ItemID = 'A123'

            AND WHID = 333

GROUP BY    ItemID

Here, the DISTINCT keyword is more relevant, because in a COUNT it might make sense to eliminate the duplicates. COUNT is commonly used to return the number of rows in a table; again using the InvMst table as an example, I'd use the following to calculate how many records InvMst has:

SELECT            COUNT(*)

FROM        InvMst

Note that COUNT returns a DECIMAL(15, 0) field; you need to use COUNT_BIG when the expected result is greater than the maximum value of an integer. COUNT_BIG works exactly like COUNT, with the difference that it can return a DECIMAL(31, 0) value as maximum output.

Some other times, you just need the maximum or minimum of a set of values. You can use MAX and MIN, respectively, to get those results. These two functions are similar to COUNT in every way, but using the DISTINCT keyword has no practical effect. If I wanted to return the minimum and maximum expiration dates of item 'A123' in warehouse 333, regardless of the shelf it sits in, I'd use the following statement:

SELECT            MIN(ItemQty)

            , MAX(ItemQty)

FROM        InvMst

WHERE       WHID = 333

As you can see from this example, you can use multiple column functions in the same statement. However, due to their specific nature, some are not compatible with each other and can't be used together.

Now that you're getting the hang of it, the next articles will discuss the scalar functions. Don't know what they are? Well, here's a sneak peek of what's coming: a scalar function takes input arguments and returns a single value result. The restrictions on the use of aggregate functions do not apply to scalar functions, because a scalar function is applied to single parameter values rather than to sets of values.