Aggregation functions
Total, count and compare the values in a list in one call.
Syntax
Write the function's name and the list, with a condition where the function asks for one.
SUM(values)
AVERAGE(values)
COUNT(values)
COUNTA(values)
COUNTIF(values, criteria)
MAXIFS(max_values, criteria_values, criteria)
AVERAGEIF(values, criteria, [average_values])
MINIFS(min_values, criteria_values, criteria)
SUMPRODUCT(list1, list2)
FREQUENCY(values, bins)
MEDIAN(values)
AVERAGEIFS(average_values, criteria_values, criteria)
COUNTIFS(range, criteria)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
SUM
Required: YesvaluesAVERAGE
Required: YesvaluesCOUNT
Required: YesvaluesCOUNTA
Required: YesvaluesCOUNTIF
Required: Yesvalues
Required: YescriteriaMAXIFS
Required: Yesmax_values
Required: Yescriteria_values
Required: YescriteriaAVERAGEIF
Required: Yesvalues
Required: Yescriteria
Required: Noaverage_valuesMINIFS
Required: Yesmin_values
Required: Yescriteria_values
Required: YescriteriaSUMPRODUCT
Required: Yeslist1
Required: Yeslist2FREQUENCY
Required: Yesvalues
Required: YesbinsMEDIAN
Required: YesvaluesAVERAGEIFS
Required: Yesaverage_values
Required: Yescriteria_values
Required: YescriteriaCOUNTIFS
Required: Yesrange
Required: Yescriteria
Examples
Five item prices held once: the total, the average rounded to cents, the middle price and the number of items.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
SUM | decimal | The total | SUM([1, 2, 3]) |
AVERAGE | decimal | The mean | AVERAGE([1, 2, 3, 4]) |
COUNT | whole number | How many are numbers | COUNT([1, 2, 3]) |
COUNTA | whole number | How many are not null. Empty text counts as a value | COUNTA([1, "a", null]) |
COUNTIF | whole number | How many meet the test | COUNTIF([1, 2, 3, 4], ">2") |
MAXIFS | decimal | The largest value whose paired value meets the test | MAXIFS([1, 2, 3, 4], [1, 2, 3, 4], "<3") |
AVERAGEIF | decimal | The mean of the values that meet the test | AVERAGEIF([1, 2, 3, 4], ">2") |
MINIFS | decimal | The smallest value whose paired value meets the test | MINIFS([1, 2, 3, 4], [1, 2, 3, 4], ">2") |
SUMPRODUCT | decimal | The sum of the pairwise products | SUMPRODUCT([1, 2, 3], [4, 5, 6]) |
FREQUENCY | list of whole numbers | How many values fall in each group, with a last group for values above the highest limit | FREQUENCY([1, 2, 2, 3], [2]) |
MEDIAN | decimal | The middle value | MEDIAN([1, 3, 2, 10]) |
AVERAGEIFS | decimal | The mean of the values where every test is met. No match is refused | AVERAGEIFS([120, 80, 200], [1, 2, 1], "<3") |
COUNTIFS | whole number | How many positions meet every test | COUNTIFS([120, 80, 200], ">100", [1, 2, 1], "<3") |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An argument is not a list of numbers, a COUNTIF test is not text ("Expected a String criteria for COUNTIF function."), or paired lists differ in length ("Arrays must have same length") | Pass lists of numbers, a test in quotes and lists of equal length |
Limitations
- A test is written as text with an operator and a value, such as
">2". - Write the list in square brackets.
- Lists that are paired, as in
SUMPRODUCTandMAXIFS, must be the same length. - Coming:
SUMIFandSUMIFSdo not run:SUMIF([1, 2, 3, 4], ">2")answers[HTQL-2001] Expected an array of floating-point values, andCOUNTIFSanswers[HTQL-1006] Not yet supported: COUNTIFS. UseSUMPRODUCTorMAXIFSandAVERAGEIFfor a conditional total or comparison until they are installed.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use LEARN to total a table in the database, and these functions for a list you already hold.
- Use COUNTIF for a count that depends on a test.