Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

From Ilianko

← Back to DP-420 Terminology Reference

Category: Server-Side Programming

Summary: Built-in SQL aggregate operators (COUNT, SUM, AVG, MIN, MAX) used with GROUP BY.

Overview

Azure Cosmos DB for NoSQL supports standard built-in aggregate functions in SQL queries, typically paired with the GROUP BY clause to summarize and group items across partitions:

  • COUNT: Returns the number of items in the group (e.g., COUNT(1) or COUNT(*) to count all items, or COUNT(c.property) to count items where the property is defined and not null).
  • SUM: Returns the arithmetic sum of numeric values in the expression across the group (ignores non-numeric and undefined values).
  • AVG: Returns the arithmetic average (mean) of numeric values in the expression across the group.
  • MIN: Returns the minimum scalar value (numeric, string, boolean, etc.) in the expression across the group.
  • MAX: Returns the maximum scalar value (numeric, string, boolean, etc.) in the expression across the group.

Key DP-420 Rules & Behaviors:

  • GROUP BY Rule: Any property projected in the SELECT clause that is not wrapped inside an aggregate function MUST be explicitly included in the GROUP BY clause.
  • Exact Function Names: Only COUNT, SUM, AVG, MIN, and MAX are supported out of the box in the NoSQL query language. Functions such as MEDIAN, STDEV, or VAR are NOT natively supported.
  • VALUE Keyword: You can use SELECT VALUE COUNT(1) FROM c to project a single scalar value rather than a JSON object.
  • Performance & Indexing: Queries with GROUP BY and aggregate functions benefit significantly from composite indexes on the grouped/filtered properties and execute efficient per-partition aggregations that the query engine merges.


Example 1 (Cosmos DB SQL): Comprehensive GROUP BY with all 5 standard aggregate functions 

-- Groups products by category and calculates count, sum, average, min, and max price
SELECT 
    c.category,
    COUNT(1) AS TotalProducts,
    SUM(c.price) AS TotalInventoryValue,
    AVG(c.price) AS AveragePrice,
    MIN(c.price) AS LowestPrice,
    MAX(c.price) AS HighestPrice
FROM c
WHERE c.isAvailable = true
GROUP BY c.category

Every scalar property in the SELECT list (c.category) is present in the GROUP BY clause, and all 5 standard aggregates are computed per group.

Example 2 (Cosmos DB SQL): Multi-Property GROUP BY with WHERE and ORDER BY 

-- Groups items by multiple properties (manufacturer and category) with an average rating
SELECT 
    c.manufacturer,
    c.category,
    COUNT(1) AS ItemCount,
    AVG(c.rating) AS AverageRating,
    SUM(c.inventoryCount) AS TotalInventory
FROM c
WHERE c.discontinued = false
GROUP BY c.manufacturer, c.category
ORDER BY c.manufacturer ASC

Example 3 (Cosmos DB SQL): Scalar Aggregate with the VALUE keyword (No GROUP BY) 

-- Returns a raw scalar number (e.g. 42) instead of a JSON object ({"$1": 42})
SELECT VALUE COUNT(1)
FROM c
WHERE c.status = "Pending"

More Info & Links

DP-420 Style Practice Questions

Q1. Which set of aggregate functions is natively supported in Azure Cosmos DB for NoSQL with the GROUP BY clause?

  • A. COUNT, SUM, AVG, MIN, and MAX
  • B. COUNT, MEDIAN, STDEV, VAR, and MODE
  • C. SUM, AVERAGE, MINIMUM, MAXIMUM, and TOTAL
  • D. COUNT, FIRST, LAST, SUM, and AGGREGATE

Q2. When executing a SQL query with GROUP BY in Azure Cosmos DB, what is required for properties in the SELECT projection?

  • A. Any scalar property in the SELECT list that is not wrapped in an aggregate function must appear in the GROUP BY clause
  • B. The query can only project a single aggregate function with no category or group keys
  • C. The container's partition key must always be the first item in the GROUP BY list
  • D. Aliasing with the AS keyword is prohibited when using GROUP BY

Answer Key

Answers are listed at the very end so you can attempt the questions first.

  • Q1 (Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)): A. COUNT, SUM, AVG, MIN, and MAX — Azure Cosmos DB for NoSQL supports five exact built-in aggregate functions: COUNT, SUM, AVG, MIN, and MAX. Other statistical functions like MEDIAN or STDEV are not natively built-in.
  • Q2 (Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)): A. Any scalar property in the SELECT list that is not wrapped in an aggregate function must appear in the GROUP BY clause — In Azure Cosmos DB SQL, any non-aggregate property in the SELECT clause must be explicitly declared in the GROUP BY clause.

DP-420: Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB — term detail page

Retrieved from "https://ilianko.com/index.php?title=Aggregate_Functions_(COUNT,_SUM,_AVG,_MIN,_MAX)&oldid=6205"