Functions

Once your database contains data, you can use functions on the data to return a result for a set of column values (using aggregate functions) or accept one or more parameters as input and return a single value (using scalar functions).

Aggregate Functions

An aggregate function is a function that returns a single result for a given set of column values. Pervasive PSQL supports the aggregate functions shown in Table 46.

Table 46

Determines the average of a group of values. If the operand is not a DECIMAL, then AVG returns an 8-byte FLOAT. If the operand is a DECIMAL, AVG returns a 10-byte DECIMAL.

COUNT

Counts the number of rows in a specified group. COUNT always returns a 4-byte INTEGER.

DISTINCT

Include the DISTINCT keyword in your SELECT statement to direct Pervasive PSQL to remove duplicate values from the result. By using DISTINCT, you can retrieve all unique rows that match the SELECT statement's specifications.

MAX

Returns the maximum value of a group of values. MAX returns the same data type and size as the operand.

MIN

Returns the minimum value of a group of values. MIN returns the same data type and size as the operand.

SUM

Determines the sum of a group of values. If the operand is not a DECIMAL, then SUM returns an 8-byte FLOAT. If the operand is a DECIMAL, SUM returns a 10-byte DECIMAL.

For more information about each of these functions, refer to the SQL Engine Reference.

Arguments to Aggregate Functions

For AVG and SUM functions, the argument to the function must be the name of a numeric column. The COUNT, MIN, and MAX functions can provide results on numeric or non-numeric columns.

You cannot nest aggregate function references. For example, the following reference is not valid:

SUM(AVG(Cost_Per_Credit))

You can use aggregate functions in an expression, as in the following example:

AVG(Cost_Per_Credit) + 20

You can also use an expression as an argument to a group aggregate function. For example, the following expression is valid:

AVG(Cost_Per_Credit + 20)

The aggregate functions treat null column values as significant. For example, on a table that contains 40 rows of data and 5 rows of null values, the COUNT function returns 45.

You can use the DISTINCT keyword to force Pervasive PSQL to treat all null column values as a single value. The following example calculates the average column value in the Grade column:

AVG(DISTINCT Grade)

The DISTINCT keyword affects the AVG, COUNT, and SUM functions. It has no effect on the MIN and MAX functions.

Aggregate Function Rules

You can use aggregate functions in a SELECT statement as follows:

•

As items in the selection list.

•

In a HAVING clause.

Generally, you use aggregate functions in a SELECT statement that contains a GROUP BY clause to determine aggregate values for certain groups of rows. However, if the SELECT statement does not contain a GROUP BY clause and you want to use aggregate functions in it, all the items in the selection list must be aggregate functions.

If the SELECT statement does contain a GROUP BY clause, the column or columns specified in the GROUP BY clause must be select terms that are single columns, not aggregate functions. All the select terms that are not also listed in the GROUP BY clause, however, must be aggregate functions.

The following example returns a result table that allows you to determine the amount each student has paid.

SELECT Student_ID, SUM(Amount_Paid)
FROM Billing
GROUP BY Student_ID;

You can also include aggregate functions in HAVING clauses used with a GROUP BY clause. Using the HAVING clause with a GROUP BY clause restricts the groups of rows Pervasive PSQL returns. Pervasive PSQL performs the aggregate function on the column of each group of rows specified in the GROUP BY clause, and returns a single result for each set of rows that has the same value for the grouping column.

In the following example, Pervasive PSQL returns row groups only for students currently enrolled with more than 15 credit hours:

SELECT Student_ID, SUM(Credit_Hours)
FROM Enrolls e, Class cl, Course c
WHERE e.Class_ID = cl.ID AND cl.Name = c.Name
GROUP BY Student_ID
HAVING SUM(Credit_Hours) > 15;

Scalar Functions

Scalar functions such as CONCAT and CURDATE accept one or more parameters as input and return a single value. For example, the LENGTH function returns the length of a string column value. You can use scalar functions in Pervasive PSQL statements that allow computed columns in expressions.

The type of expression operator you can use depends on the type of result the function returns. For example, if the function returns a numeric value, you can use arithmetic operators. If the function returns a string value, you can use string operators.

You can nest scalar functions, but each nested function must return a result that is an appropriate parameter to the next level scalar function, as in the following example:

SELECT RIGHT (LEFT (Last_Name, 3), 1)
FROM Person;

Pervasive PSQL executes the LEFT function first. If the value in the Last Name column is Baldwin, the string resulting from the LEFT function is Bal. This string is the parameter of the RIGHT function, which returns ‘l’ as the rightmost character of the string.

You can use scalar functions that return a numeric result within a computed column that calculates a numeric value. You can also use scalar functions that return a string value as an expression to another string function, but the total length of the string result cannot exceed 255 bytes.

In SQL Engine Reference, see Bitwise Operators for complete information on scalar functions you can use with Pervasive PSQL.