SUM_EACH

Stores the sum of values per group.

Syntax

SUM_EACH([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: The maximum number of entries that can be counted (default: 2,147,483,647). Beyond that, entries will be discarded. In some cases, depending on the data distribution, discarding groups via MAX VALUES may cause some data to be discarded from groups that would otherwise have been returned. MAX VALUES is optional. If it is omitted there is, in effect, no limit. GROUP: The field you would like counted. VALUE: The field that is summed. ‌

Returns

A number

Notes

SUM_EACH can be used only with hierarchical output types. SUM_EACH returns a mapping of the key value where the key is the GROUP, and the value is the summation of the VALUE field over all the entries that have the same GROUP key. In the example below, all the "scores" for each "user" key are summed.

Example

Data

[
   {
      "user":"A01",
      "level":1,
      "score":25
   },
   {
      "user":"A01",
      "level":2,
      "score":48
   },
   {
      "user":"A01",
      "level":2,
      "score":81
   },
   {
      "user":"B02",
      "level":1,
      "score":23
   },
   {
      "user":"B02",
      "level":1,
      "score":29
   }
]

Query

Find the total score for each user:

SELECT
   SUM_EACH(data.user, data.score) AS sum_each_data_score__data_level 
FROM
   "MAX_SAMPLE_DATA - json"

Results

{
   "sum_each_data_score__data_level":[
      {
         "key":"A01",
         "value":154
      },
      {
         "key":"B02",
         "value":52
      }
   ]
}

Related Functions

SUM

Dialog