Aggregate functions: COUNT, MIN, MAX, SUM, AVG

These functions aggregate an entire column of values into a single scalar result. For decimal data types:

For the MIN, MAX, and AVG functions, the result’s precision and scale are the same as the precision and scale of the operand.

For the SUM function, the result’s precision and scale are (P, s), where P is the maximum precision in the database or the Integration service, and s is the scale of the operand.

The COUNT function reduces any argument type to a single integer representing the number of non-NULL items. As in SQL-92, COUNT(*) counts the number of rows in a table:

Integer COUNT( column )

Example

The following code:

SELECT COUNT(ORDERS.ORDERID) AS NumberOfOrders

FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS

returns:

111

MIN and MAX accept any type and return the minimum or maximum value, using the same rules that apply to comparison of individual items:

ColumnType MIN( column )

ColumnType MAX( column )

Examples

The following code:

SELECT MIN(ITEMS.QUANTITY)

FROM "../Data Sources/MyDatabase/ITEMS.SMA" ITEMS

returns:

2

The following code:

SELECT MAX(ITEMS.QUANTITY)

FROM "../Data Sources/MyDatabase/ITEMS.SMA" ITEMS

returns:

6203

SUM and AVG can be applied to any of the three numeric types and produce the sum or average of all the numbers:

ColumnType SUM( column )

ColumnType AVG( column )

Examples

The following code:

SELECT SUM(ITEMS.QUANTITY)

FROM "../Data Sources/MyDatabase/ITEMS.SMA" ITEMS

returns:

606177

The following code:

SELECT AVG(ITEMS.QUANTITY)

FROM "../Data Sources/MyDatabase/ITEMS.SMA" ITEMS

returns:

319

Copyright Actuate Corporation 2012