Aggregate Functions

Level 3 — CRUD Operations in SurrealQL The mathematical and collection functions in SurrealQL used inside grouping queries to compile calculations across records, including count(), math::sum(), math::mean(), and array::group().

1. Prerequisites

• GROUP BY / GROUP ALL — The aggregation context.
• SELECT — Executing SELECT queries before aggregating results.

2. Term Category

Query Feature (array and record set aggregation functions): - Database Command / Tool

3. Explanation

(1) Design Motivation — "Why did we design this?"

When grouping records, you compress many individual rows into a single output row.

To make sense of the grouped data, you need functions that can calculate summaries:

• How many rows are in this group?
• What is the total sum of their balances?
• What is the average price?

In standard SQL, you use global functions like COUNT(), SUM(), and AVG().

In SurrealDB, functions are organized into Namespaced Libraries (like math::* and array::*) to keep the query language clean and prevent naming collisions.

This provides a consistent interface for executing mathematical and list calculations across grouped record sets.

(2) Core Aggregate Functions

• count(): Counts the number of matching records in the group.
  • Syntax: count() (no arguments required, unlike SQL's COUNT(*)).
• math::sum(<field>): Calculates the mathematical sum of numeric values in the group.
• math::mean(<field>): Calculates the arithmetic average (mean) of values.
  • Note: SurrealDB uses math::mean(), corresponding to SQL's AVG().
• math::min(<field>) / math::max(<field>): Returns the minimum or maximum value in the group.
• array::group(<field>): Collects all individual values of a field within the group and merges them into a single nested array.

(3) Reality Metaphor (Ledger Helpers)

Imagine analyzing folders in a filing cabinet drawer:

• count(): A Tally Clicker. Every time you check a folder, you click the button once. You get a count.
• math::sum(): A Pocket Calculator. You look at the invoice amount in each folder, keying in the numbers and hitting the + button to accumulate a total.
• array::group(): A Plastic Baggage Enclosure. Instead of counting or adding details, you take the name tags out of every folder in the drawer, toss them all into the plastic bag, and attach the bag to the drawer. You get a list of all names.

(4) Code Examples

Running Aggregations in SurrealQL

Let's analyze store sales data:

SELECT
  category,
  count() AS item_count,               -- Count of items in this category
  math::sum(price) AS category_revenue, -- Total sum of prices
  math::mean(price) AS average_price,   -- Average price (mean)
  math::min(price) AS cheapest_item,    -- Minimum price
  math::max(price) AS priciest_item,    -- Maximum price
  array::group(name) AS product_names   -- List of all product names in this group
FROM products
GROUP BY category;

4. Common Mistakes & Pitfalls

Mistake 1: Writing the standard SQL function 'AVG()' instead of 'math::mean()' to calculate averages, triggering syntax errors

The mistake: Writing a query like SELECT AVG(price) FROM products GROUP ALL; based on SQL habits.

Why it's wrong: SurrealQL does not have a global, non-namespaced AVG() function.

Attempting to run it will cause the database query compiler to throw an unrecognized function exception.

Fix: Namespace the calculation correctly using math::mean():

-- BAD
SELECT AVG(price) FROM products GROUP ALL;

-- GOOD
SELECT math::mean(price) FROM products GROUP ALL;

Mistake 2: Using count() Without GROUP BY when Non-Aggregated Fields Are Selected

The mistake: Writing SELECT name, count() FROM user; without specifying GROUP BY.

Why it's wrong: Selecting non-aggregated columns alongside aggregate functions without a GROUP BY clause causes ambiguous group evaluation errors or returns un-grouped results.

Incorrect:

-- Ambiguous non-grouped query
SELECT status, count() FROM user; // ❌ Missing GROUP BY status!

Fix:

SELECT status, count() FROM user GROUP BY status; // Correct grouping

Mistake 3: Expecting math::mean() or math::sum() to Ignore Non-Numeric Array Elements

The mistake: Passing arrays containing strings or NONE into math::sum([10, "20", NULL]).

Why it's wrong: Aggregate functions expect numeric values. Un-cast string values or nullish values generate runtime math errors. Clean arrays with array::filter() first.

Incorrect:

RETURN math::sum([10, "20"]); // ❌ Mixed non-numeric elements!

Fix:

RETURN math::sum([10, <number> "20"]); // Explicit numeric casting

5. Practice Exercises

Exercise 1: Computing Order Totals with math::sum()

Scenario: An e-commerce reporting service calculates the total revenue generated from completed order line items stored in table order_item.

Requirements:

1. Insert 3 order_item records with decimal prices (19.99dec, 49.50dec, 120.00dec).
2. Write a SurrealQL query calculating total revenue using math::sum().

CREATE order_item:1 SET price = 19.99dec;
CREATE order_item:2 SET price = 49.50dec;
CREATE order_item:3 SET price = 120.00dec;

-- Calculate total order revenue sum
SELECT math::sum(price) AS total_revenue FROM order_item;

Exercise 2: Grouped Minimum and Maximum Price Analysis

Scenario: A product catalog analytics service computes the minimum and maximum product price per product category (category).

Requirements:

1. Group products by category.
2. Compute math::min(price) and math::max(price) for each category.

CREATE product:p1 SET category = "electronics", price = 199.99dec;
CREATE product:p2 SET category = "electronics", price = 899.99dec;
CREATE product:p3 SET category = "books", price = 15.00dec;

-- Group products by category and calculate min/max price bounds
SELECT 
    category,
    math::min(price) AS min_price,
    math::max(price) AS max_price
FROM product
GROUP BY category;

Exercise 3: Record Set Counting with count()

Scenario: A user metrics dashboard counts the total number of active user accounts stored in table user.

Requirements:

1. Write a SELECT query calculating active user count using count().

CREATE user:u1 SET active = true;
CREATE user:u2 SET active = true;
CREATE user:u3 SET active = false;

-- Count active users
SELECT count() AS total_active FROM user WHERE active = true GROUP ALL;

6. Related Terms

• GROUP BY / GROUP ALL — The aggregation context.
• Math Functions (math::*) — Related concept: Math Functions (math::*).

7. Key Takeaways

• Aggregate functions compute summary calculations across grouped records.
• Standard math functions are namespaced inside the math::* library path.
• count() calculates row totals (syntax uses empty parenthesis: count()).
• math::mean() calculates arithmetic averages, replacing SQL's AVG().
• math::sum(), math::min(), and math::max() handle numeric properties.
• array::group() gathers values from grouped records into a single nested array.
• Attempting to run un-namespaced aggregate functions triggers parser errors.