Using Aggregate Functions

You can use built in aggregate functions to find information such as a count, a sum, an average, a minimum, or a maximum.

The following functions are called SQL aggregate functions, because their semantics are similar to those in standard SQL: they work in conjunction with grouping and they aggregate values across the rows of a group. The aggregate functions can be used only in the SELECT or ORDER BY clauses, and they cannot be nested.

If you want to follow along with the examples, create tables and insert the data as described in the Tables used in the Examples topic.

The following aggregate functions are supported:

long count(*)
long count(any*)
number sum(any*)
number avg(any*)
any_atomic min(any*)
any_atomic max(any*)
ARRAY(any) array_collect(DISTINCT any*)
ARRAY(any) array_collect(any*)
long count(DISTINCT any*)

Note:

All SQL aggregate function names are case sensitive.

count(*) function

The count star function returns the number of rows in a group.

Syntax:

long count(*)

Semantics:

The count star function calculates the number of records fetched by the query.

return type: long

Example 6-14 Find the total number of passengers who have contact details in their records

SELECT count(*) AS COUNT_PASSENGER 
FROM BaggageInfo bag
WHERE length(contactPhone) > 0

Explanation: In an airline baggage tracking application, you can calculate the total count of passengers who have furnished their contact details. The contactPhone field in the BaggageInfo table includes the contact details of the passengers. You use the count star function to find the number of passenger records with the contactPhone entry. There is a possibility that the contact details include NULL values and empty strings. You can use the length function in the WHERE clause to exclude such rows from being counted. If a contactPhone has an empty or a NULL value, the length function returns a NULL value. You apply a value comparison to select only the rows that yield a value greater than 0, and then determine the total count of the resultant rows using the count star function.

Output:

{"COUNT_PASSENGER":21}

Example 6-15 Find the number of checked bags that arrived on the 1st of Feb 2019

SELECT count(*) AS COUNT_BAGS
FROM BaggageInfo bag,
EXTRACT(DAY FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE $t3=2019 AND $t2=02 AND $t1=01

Explanation: In the airline baggage tracking application, you can get the total count of checked bags on a particular date. The bagArrivalDate field in the BaggageInfo table contains the arrival date of the passenger's checked bags. In the above query, you count the number of rows that have the bagArrivalDate as 1st of Feb 2019 to fetch the number of checked bags on the given date. You use several filter conditions here to extract only the date part of the timestamp. You first use the CAST operator to convert the bagArrivalDate to a timestamp and then extract the date, month, and year details from the timestamp using the EXTRACT expression. You use the value comparison to determine if the day, month, and year correspond to the required date value, that is, 01,02, and 2019 respectively. You then use the logical operator AND to select only the rows that match all three conditions and count the resultant rows using the count function.

Output:

{"COUNT_BAGS":1}

count function

The count function computes its input expression on each row in a group and counts all the non-NULL values returned by these evaluations of the input expression.

Syntax:

long count(any*)

Semantics:

any: The count function accepts any parameter type as the input argument.

return type: long

The count star function can be used when you want to count the rows of a resultant query. It does not accept any other input argument. Whereas, the count function can be used when you want to enumerate the outcome of an input expression.

Example 6-16 Find the total number of checked bags that are estimated to arrive at the LAX airport at a particular time

SELECT $estdate as ARRIVALDATE, 
count($flight) AS COUNT
FROM BaggageInfo $bag, 
$bag.bagInfo.flightLegs.estimatedArrival $estdate,
$bag.bagInfo.flightLegs.flightNo $flight,  
$bag.bagInfo.flightLegs.fltRouteDest $flt_dest 
WHERE $estdate =any "2019-02-01T11:00:00Z" AND $flt_dest =any "LAX"
GROUP BY $estdate

Explanation: In an airline baggage tracking application, you can get the total count of checked bags that are estimated to arrive at a particular airport and time. For each flight leg, the estimatedArrival field in the flightLegs array of the BaggageInfo table contains the arrival time of the checked bags and the fltRouteDest field contains the destination airport code. In the above query, to determine the total number of checked bags arriving at the LAX airport at a given time, you first group the data with the estimated arrival time value using the GROUP BY clause. From the group, you select only the rows that have the destination airport as LAX. You then determine the bag count for the resultant rows using the count function.

Here, you can compare the string-formatted dates in ISO-8601 format due to the natural sorting order of strings without having to cast them into timestamp data types. The $bag.bagInfo.flightLegs.estimatedArrival and $bag.bagInfo.flightLegs.fltRouteDest are sequences. Since the comparison operator '=' cannot operate on sequences of more than one item, the sequence comparison operator '=any' is used instead to compare the estimatedArrival and fltRouteDest fields.

Output:

{"ARRIVALDATE":"2019-02-01T11:00:00Z","COUNT":2}

Example 6-17 Find the number of watchers for a particular show in a TV streaming application

SELECT count($a.contentStreamed[$element.showName = "Bienvenu"]) AS WATCHERS
FROM stream_acct $s, $s.acct_data $a

Explanation: In a TV streaming application, the list of shows watched by a subscriber is stored in the contentStreamed array. Each element of the array corresponds to a single show. The showName field includes the name of each show. To fetch the number of watchers for a particular show, you use the array-filter step expression in the count function. You check whether or not the showName field matches the given show (in this example, Bienvenu) for each subscriber and count the total number of such subscribers using the count function.

Output:

{"WATCHERS":4}

sum function

The sum function computes its input expression on each row in a group and sums up all the numeric values returned by these evaluations of the input expression. In fixed schema, the sum function returns an error if you try to sum any non-numeric field. In schema-less JSON, the sum function skips any non-numeric value. If all the values of the input expression are non-numeric, a NULL value is returned. The resulting value of the sum function has type long, double, or number, depending on the type of the input items:

If there is at least one input item of type number, the result will be a number.
If there is at least one item of type double or float, the result will be double, else the result will be of type long.
If the input items are a mix of long, doubles, and numbers, the result will be of type double.
If numeric values are not returned by the sum function’s input, the result is NULL.

Syntax:

long sum(any*)

Semantics:

any: The sum function accepts any parameter type as the input argument.

return type: long

Example 6-18 Find the total screen time spent on a show by a subscriber in the TV streaming application

SELECT sum($content.seriesInfo[].episodes[].minWatched) AS MINSWATCHED
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE acct_id = 1 AND $content.showName = "At the Ranch"

Explanation: In the TV streaming application, you can calculate the total screen time spent on a show by each subscriber. Each subscriber is associated with a unique account ID, stored in the acct_id field of the stream_acct table. The information of the shows is stored in the contentStreamed array. The showName field holds the name of the show and the minWatched field stores the time lapsed for each episode in each season of the show. In this query, you use the sum function to add the values of the minWatched fields of all the episodes in all the seasons to calculate the total screen time for the subscriber with account ID 1 and show named At the Ranch.

Output:

{"MINSWATCHED":225}

avg function

The avg (average) function computes its input expression on each row in a group and sums up as well as counts all the numeric values returned by these evaluations of the input expression. Any non-numeric values are skipped. An error message is returned if the input expression does not return any numeric values. The resulting value is the division of the sum by the count. This value has type double, or number, depending on the type of the input items.

If there is at least one input item of type number, the result will be a number, else the result will be double.
If numeric values are not returned by avg function's input, the result is NULL.

Syntax:

number avg(any*)

Semantics:

any: The avg function accepts any parameter type as the input argument.

return type: number

Example 6-19 Find the average screen time spent on a show by a subscriber

SELECT avg($content.seriesInfo[].episodes[].minWatched) AS AVERAGETIME
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE acct_id = 1 AND $content.showName = "At the Ranch"

Explanation: In the TV streaming application, you can calculate the average screen time spent on a show by each subscriber. Each subscriber is associated with a unique account ID, stored in the acct_id field of the stream_acct table. The information of the shows is stored in the contentStreamed array. The showName field holds the name of the series and the minWatched field stores the time lapsed for each episode in each season of the show. In this query, you use the avg function on the minWatched field to calculate the average screen time spent on the show named At the Ranch by a subscriber with account id 1. The avg function first calculates the total screen time by adding the values in the minWatched fields for the given show. It then divides the sum by the number of minWatched fields to calculate the average value.

Output:

{"AVERAGETIME":56.25}

min function

The min function returns the minimum value among all the values returned by the evaluations of the input expression on each row in a group. The input expression is evaluated as follows:

An error is displayed if it can be determined during the compile time that the values returned by the input expression belong to a type for which an order comparison is not defined (for example, RECORD, MAP, BINARY, or FIXED_BINARY). Otherwise, the min value for each group is initialized to NULL.
Let M be the current minimum value and N be the next input value. The M and N are compared using Value Comparison Operators. If M is NULL, M is set to N. Else, if N is less than M, that means N can be the minimum value and hence M is set to N, and N is set to the next input value. This is continued until all the values in the input expression are compared and a minimum value is ascertained. When the values are not comparable, the following order is used:
```
numeric values < timestamps < strings and enums < booleans
```
If N is a record, map, array, binary, or fixed binary value, NULL, or JSON null, it is skipped and the next input value is considered.

Syntax:

any_atomic min(any*)

Semantics:

any: The min function accepts any parameter type as the input argument.

return type: atomic data type

max function

The max function returns the maximum value in all the sequences returned by the evaluations of the input expression on each row in a group. The specific rules are the same as for the max function, except that the current max value M will be replaced by the next input value N if N is not skipped and is greater than M.

Syntax:

any_atomic max(any*)

Semantics:

any: The max function accepts any parameter type as the input argument.

return type: atomic data type

Example 6-20 For a given show, find the minimum and maximum screen time

SELECT min($content.seriesInfo[].episodes[].lengthMin) AS MINTIME
max($content.seriesInfo[].episodes[].lengthMin) AS MAXTIME
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE $content.showName = "At the Ranch"

Explanation: In the TV streaming application, you can find the minimum and maximum duration of a show. The lengthMin field in the stream_acct table stores the length of each episode for a show. In this query, you use:

The min function on the lengthMin field to fetch the duration of the episode from the show At the Ranch that has the least screen time.
The max function on the lengthMin field to fetch the duration of the episode from the show At the Ranch that has the most screen time.

Output:

{"MINTIME":45,"MAXTIME":85}

Example 6-21 Aggregate Function - Fetch the age and average income of users from the User data table

CREATE INDEX idx11 ON users (age)

SELECT 
age, count(*) AS count, avg(income) AS income 
FROM users 
GROUP BY age

Explanation: Consider an application that maintains the user data. See User data application table in the Tables used in the Examples section. The users table includes multiple records of users with the same age group. You use the aggregate functions to retrieve the count of such subscribers and their average income. The above query groups users by their age, and for each age, returns the number of users with the same age group and their average income.

Output:

{"age":22,"count":2,"income":50000.0}
{"age":45,"count":1,"income":75000.0}

array_collect(DISTINCT any*) function

The array_collect(DISTINCT any*) function computes the input expression on each row of a group and collects all the resulting distinct non NULL values into an array.

Syntax:

ARRAY(any) array_collect(DISTINCT any*)

Semantics:

The array_collect function computes the input expression on each row of a group. The input expression may be any kind of expression, except a SELECT expression. It collects all the resulting distinct values (except NULL values) into an array. The function returns the constructed array.

Note:

DISTINCT causes values to be compared for equality using the semantics of the value comparison operator, with the following exceptions: strings are comparable with strings only (not enums and timestamps), enums are comparable with enums only (not strings), and timestamps are comparable with timestamps only (not strings).

Example 6-22 In the TV streaming application, return the set of distinct show ids (as an array) that have been viewed in every country.

SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.showId) AS shows 
FROM stream_acct acct group by acct.acct_data.country

Explanation: The TV streaming application streams various shows that are watched by customers across the globe. Here you want a list of distinct show ids for every country. You group the data based on the country and list the show ids for every country as an array. You eliminate duplicate show ids with the distinct operator.

Output:

{"country":"USA","shows":[16,15]}
{"country":"France","shows":[15]}
{"country":"Germany","shows":[26,15]}

Example 6-23 In the TV streaming application, return the set of distinct genres (as an array) that have been viewed by customers in every country.

SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.genres[]) AS genres 
FROM stream_acct acct group by acct.acct_data.country

Explanation: The TV streaming application streams various shows that are watched by customers across the globe. Here you want a list of distinct genres of shows watched by customers in every country. You group the data based on the country and for each country, you fetch the genres of all shows into a single array using acct.acct_data.contentStreamed.genres[].You eliminate duplicate genres with the distinct operator.

Note:

In the above query, you use acct.acct_data.contentStreamed.genres[] as you want the details of all the genres to be fetched in a single array. If you omit [] in acct.acct_data.contentStreamed.genres, details of the genres will be fetched as independent arrays for each row and distinct will be applied only on independent arrays and not in the entire set as shown below.

SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.genres) AS genres
FROM stream_acct acct group by acct.acct_data.country

Output:


{"country":"USA","genres":[["comedy","drama","danish"],["comedy","french"]]}
{"country":"France","genres":[["comedy","french"]]}
{"country":"Germany","genres":[["action","crime","spanish"],["comedy","french"]]}

In the above example, you can see that the data is grouped based on the country and for each country, the genres watched by customers are fetched as a row-level array. For example, for the country USA, you see 2 arrays of genres. The DISTINCT is applied at the array level. Only if the arrays are identical, then DISTINCT fetches only one array. Else all the arrays are fetched back as shown in the result above.

Output:

{"country":"USA","genres":["drama","danish","comedy","french"]}
{"country":"France","genres":["comedy","french"]}
{"country":"Germany","genres":["spanish","comedy","action","crime","french"]}

array_collect(any*) function

The array_collect(any*) function computes the input expression on each row of a group and collects all the resulting non NULL values into an array.

Syntax:

ARRAY(any) array_collect(any*)

Semantics:

The array_collect function computes the input expression on each row of a group. The input expression may be any kind of expression, except a SELECT expression. The function collects all the resulting values (except NULL values) into an array and returns the populated array. The array_collect function permits duplicate values to be inserted into the array.

Example 6-24 In the TV streaming application, return the first name and last name of customers (as an array) in USA and Germany.

SELECT acct.acct_data.country,
array_collect(
{"firstName":acct.acct_data.firstName,
"lastName":acct.acct_data.lastName}) as user_info
FROM stream_acct acct WHERE 
acct.acct_data.country IN ('USA','Germany') 
group by acct.acct_data.country

Explanation: The TV streaming application streams various shows that are watched by customers across the globe. Here you want to fetch the user information (first name and last name) of customers in USA and Germany. You group the data based on the country and filter the data only for two countries USA and Germany. For each of these two countries , you fetch the first name and last name of all the customers and populate that in a single array.

Output:

{"country":"USA","user_info":[{"firstName":"John","lastName":"Lewis"}]}
{"country":"Germany","user_info":[{"firstName":"Angela","lastName":"Mercel"}]}

long count(DISTINCT any*) function

The count function computes the input expression on each row of a group and counts all the distinct non-NULL values returned by the input expression.

Syntax:

long count(DISTINCT any*)

Semantics:

The count function computes the input expression on each row of a group. The input expression may be any kind of expression other than a subquery. The function counts all the distinct non-NULL values returned by the input expression. The return type for the count function is long.

Note:

DISTINCT causes values to be compared for equality using the semantics of the value comparison operator with the following exceptions: strings are comparable with strings only (not enums and timestamps), enums are comparable with enums only (not strings), and timestamps are comparable with timestamps only (not strings).

Example 6-25 In the TV streaming application, return the count of distinct show ids for every genre viewed by customers.

SELECT $genre, count(distinct $content.showId) AS show_count 
FROM stream_acct acct, acct.acct_data.contentStreamed[] as $content, 
$content.genres[] as $genre, $content.showId as $showid group by $genre

Explanation: The TV streaming application streams various shows that are watched by customers across the globe. Here you want a count of distinct shows in every genre viewed by customers. The details of content streamed is fetched into a single array using acct.acct_data.contentStreamed[]. You group the data based on the genres and for every genre , you fetch the list of showid as an array. You eliminate duplicate show ids with the distinct operator. Then the count of distinct show ids for every genre is returned.

Note:

In the above query, if you omit [] in acct.acct_data.contentStreamed, details of the content streamed will be fetched as independent arrays for each row and count(distinct )will be applied only on independent arrays and not in the entire set.

Output:

{"genres":"crime","show_count":1}
{"genres":"action","show_count":1}
{"genres":"comedy","show_count":2}
{"genres":"spanish","show_count":1}
{"genres":"french","show_count":1}
{"genres":"drama","show_count":1}
{"genres":"danish","show_count":1}