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

    values

    Required: Yes
  • AVERAGE

    values

    Required: Yes
  • COUNT

    values

    Required: Yes
  • COUNTA

    values

    Required: Yes
  • COUNTIF

    values

    Required: Yes
  • criteria

    Required: Yes
  • MAXIFS

    max_values

    Required: Yes
  • criteria_values

    Required: Yes
  • criteria

    Required: Yes
  • AVERAGEIF

    values

    Required: Yes
  • criteria

    Required: Yes
  • average_values

    Required: No
  • MINIFS

    min_values

    Required: Yes
  • criteria_values

    Required: Yes
  • criteria

    Required: Yes
  • SUMPRODUCT

    list1

    Required: Yes
  • list2

    Required: Yes
  • FREQUENCY

    values

    Required: Yes
  • bins

    Required: Yes
  • MEDIAN

    values

    Required: Yes
  • AVERAGEIFS

    average_values

    Required: Yes
  • criteria_values

    Required: Yes
  • criteria

    Required: Yes
  • COUNTIFS

    range

    Required: Yes
  • criteria

    Required: Yes

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.

FunctionReturnsBehaviourForm
SUMdecimalThe totalSUM([1, 2, 3])
AVERAGEdecimalThe meanAVERAGE([1, 2, 3, 4])
COUNTwhole numberHow many are numbersCOUNT([1, 2, 3])
COUNTAwhole numberHow many are not null. Empty text counts as a valueCOUNTA([1, "a", null])
COUNTIFwhole numberHow many meet the testCOUNTIF([1, 2, 3, 4], ">2")
MAXIFSdecimalThe largest value whose paired value meets the testMAXIFS([1, 2, 3, 4], [1, 2, 3, 4], "<3")
AVERAGEIFdecimalThe mean of the values that meet the testAVERAGEIF([1, 2, 3, 4], ">2")
MINIFSdecimalThe smallest value whose paired value meets the testMINIFS([1, 2, 3, 4], [1, 2, 3, 4], ">2")
SUMPRODUCTdecimalThe sum of the pairwise productsSUMPRODUCT([1, 2, 3], [4, 5, 6])
FREQUENCYlist of whole numbersHow many values fall in each group, with a last group for values above the highest limitFREQUENCY([1, 2, 2, 3], [2])
MEDIANdecimalThe middle valueMEDIAN([1, 3, 2, 10])
AVERAGEIFSdecimalThe mean of the values where every test is met. No match is refusedAVERAGEIFS([120, 80, 200], [1, 2, 1], "<3")
COUNTIFSwhole numberHow many positions meet every testCOUNTIFS([120, 80, 200], ">100", [1, 2, 1], "<3")
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An 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 SUMPRODUCT and MAXIFS, must be the same length.
  • Coming: SUMIF and SUMIFS do not run: SUMIF([1, 2, 3, 4], ">2") answers [HTQL-2001] Expected an array of floating-point values, and COUNTIFS answers [HTQL-1006] Not yet supported: COUNTIFS. Use SUMPRODUCT or MAXIFS and AVERAGEIF for 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.