Aggregate Functions

Oracle NoSQL Database offers a variety of aggregation features that allow you to compute statistics like totals, averages, minimums, maximums, and counts directly within your queries.

These functions operate across groups of records, summarizing information over vast and varied datasets.

The aggregate functions can be used only in the SELECT or ORDER BY clauses, and they cannot be nested.

Following are some of the aggregate functions:

Function	Description
count (*)	Returns the number of rows
sum (any*)	Returns the sum of all numeric values from the specified column or expression
avg (any*)	Returns the average of all numeric values from the specified column or expression
max (any*)	Returns the largest numeric value from the specified column or expression.
array_collect (DISTINCT any*)	Returns an array of unique values from the specified column or expression.
count (DISTINCT any*)	Returns the number of distinct values from the specified column or expression.

Note: To view other aggregate functions, see Using Aggregate Functions in SQL Reference Guide.

count (*) function

The count star function returns the number of rows in a group according to the WHERE clause.

Example 1 - Return the Total Number of People Whose Expense on Food Is Greater Than 1500:

SELECT count(*) AS Count_Person FROM Persons person where person.expenses.food >
    1500

Output:

{"Count_Person":3}

sum (any*) function

The sum() function adds all numeric values returned by an expression for each row in a group.

Note:

• In schema-less JSON, it ignores non-numeric values.
• If no numeric values are found, the result is NULL.

Example 2 - Find the Total Expense on Food:

SELECT count(*) AS Count_Person FROM Persons person where person.expenses.food >
    1500

Explanation:This query adds all the numeric values from expenses.food field. Rows that do not have a food expense are ignored.

Output:

{"total_food_expense":11600}

avg (any*) function

The avg() function computes the average by dividing the sum of all non-null numeric values by their count to produce the result.

Example 3 - Find the Average Spending on Clothes:

SELECT avg(person.expenses.clothes) AS avg_clothes_expense FROM Persons person

Explanation:This query calculates the average of all clothes expense values. Rows that do not have a clothes field are ignored.

Output:

{"avg_clothes_expense":1750.0}

max (any*) function

The max() function evaluates the input expression for each row in a group and returns the greatest value according to the defined ordering of the input data type.

Example 4 - Return the Person with Highest Income:

SELECT max(income) AS max_income FROM Persons

Output:

{"max_income":100000000}

array_collect (DISTINCT any*) funtion

The array_collect(DISTINCT any*) function collects the results of an expression from each row in a group and returns them as an array.

Note:

• It includes only distinct values and skips NULL values.
• The input expression can be any expression except a SELECT expression.
• DISTINCT is applied only to atomic values produced by the expression. If the expression evaluates to a non-atomic value (for example, a record, map, or array), that value is ignored by the function and therefore does not participate in the distinct set.

Example 5 - Find All the Distinct Cities People Live In:

SELECT array_collect(DISTINCT p.address.city) AS cities FROM Persons AS p

Explanation: This query returns an array of all unique city values from the address.city field and ignores any NULL values.

Output:

{"cities":["Leominster","Beloit","Middleburg","Antioch","Monroe Township"]}

count (DISTINCT any*) funtion

The count(DISTINCT any*) function counts how many unique, non-NULL values are returned by an expression for all rows in a group.

Note:

• It ignores duplicate values and NULL values.
• The result is returned as a long number.
• DISTINCT is applied only to atomic values produced by the expression. If the expression evaluates to a non-atomic value (for example, a record, map, or array), that value is ignored by the function and therefore does not participate in the distinct set. is applied only to atomic values produced by the expression. If the expression evaluates to a non-atomic value (for example, a record, map, or array), that value is ignored by the function and therefore does not participate in the distinct set.

Example 6 - Return the Number of People in Each State:

SELECT p.address.state AS state, count(DISTINCT p.id) AS people_count FROM Persons p GROUP
      BY p.address.state

Explanation: This query groups the records by state and returns each state name along with the number of distinct people in that state.

Output:

{"state":"FL","people_count":1}
{"state":"MA","people_count":1}
{"state":"NJ","people_count":1}
{"state":"TN","people_count":1}
{"state":"WI","people_count":1}

To view all the supported aggregate functions, see Using Aggregate Functions in SQL Reference Guide.