PQL SUM()

Sum() computes the sum of values in an Int, Decimal, or Timestamp field in some subset of records (defined by a row call).

Call Definition

Sum(ROW_CALL, field=FIELD)

Mandatory Arguments

• field / FIELD: the name of the field to get summed. This must be an Int, Decimal, or Timestamp field.

Optional Arguments

• ROW_CALL : the row call used to filter records to get the summation over.

Returns

• the sum of values in a field and the count of records that where included in that sum (i.e. the records returned by the row call and the records that didn’t have null values in FIELD)

Examples

Data:

Index: customer (non keyed index)

 _id | age (Int) | has_purchased (Set) | last_purchase (Timestamp)
-----+-----------+---------------------+---------------------------
 0   |    23     | ["brand1","brand2"] | 2021-01-05T08:30:00Z
 1   |    31     | ["brand1","brand3"] | 2020-09-12T12:30:00Z
 2   |    28     | ["brand1","brand3"] | 2021-08-06T16:15:00Z
 3   |    19     | []                  | null
 4   |    25     | ["brand1","brand4"] | 2021-10-01T20:45:00Z
 5   |    40     | ["brand4"]          | 2022-01-13T11:00:00Z

Example 1

What is the sum of customers age?

Query

[customer]Sum(field=age)

Tabular Response

 value | count
-------+-------
 166   | 6

HTTP Response

{
  "results": [
    {
      "value": 166,
      "floatValue": 0,
      "decimalValue": null,
      "timestampValue": "0001-01-01T00:00:00Z",
      "count": 6
    }
  ]
}

Explanation

The summation of ages for all customers is 166. Each record contributed to that summation so count is 6.

Example 2

What is the sum of ages for customers who have purchased from brand 1?

Query

[customer]Sum(Row(has_purchased=brand1), field=age)

Tabular Response

 value | count
-------+-------
 107   | 4

HTTP Response

{
  "results": [
    {
      "value": 107,
      "floatValue": 0,
      "decimalValue": null,
      "timestampValue": "0001-01-01T00:00:00Z",
      "count": 4
    }
  ]
}

Explanation

The row call returns [0,1,2,4] which is why the count is 4. The sum of their ages is 107.