> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ondb.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Aggregations

> Count, sum, average, and group data with built-in aggregation methods

QueryBuilder provides built-in aggregation methods for counting, summing, averaging, and grouping data.

## Basic Aggregations

### Count Records

```typescript theme={null}
const activeUsers = await client.queryBuilder()
  .collection('users')
  .whereField('active').equals(true)
  .count();
// Returns: number
```

### Sum a Numeric Field

```typescript theme={null}
const totalRevenue = await client.queryBuilder()
  .collection('orders')
  .whereField('status').equals('completed')
  .sumBy('amount');
// Returns: number
```

### Calculate Average

```typescript theme={null}
const avgPrice = await client.queryBuilder()
  .collection('products')
  .whereField('category').equals('electronics')
  .avgBy('price');
// Returns: number
```

### Find Maximum Value

```typescript theme={null}
const highestPrice = await client.queryBuilder()
  .collection('products')
  .maxBy('price');
// Returns: T | null
```

### Find Minimum Value

```typescript theme={null}
const lowestPrice = await client.queryBuilder()
  .collection('products')
  .minBy('price');
// Returns: T | null
```

### Get Distinct Values

```typescript theme={null}
const categories = await client.queryBuilder()
  .collection('products')
  .distinctBy('category');
// Returns: string[]
```

### Count Distinct Values

```typescript theme={null}
const uniqueCategories = await client.queryBuilder()
  .collection('products')
  .countDistinct('category');
// Returns: number
```

## Grouped Aggregations

Use `groupBy()` to perform aggregations on groups of records.

### Count by Group

```typescript theme={null}
// Count users by country
const usersByCountry = await client.queryBuilder()
  .collection('users')
  .groupBy('country')
  .count();
// Returns: { "USA": 150, "UK": 75, "Germany": 50 }
```

### Sum by Group

```typescript theme={null}
// Sum order amounts by category
const salesByCategory = await client.queryBuilder()
  .collection('orders')
  .whereField('status').equals('completed')
  .groupBy('category')
  .sumBy('amount');
// Returns: { "electronics": 50000, "clothing": 25000 }
```

### Average by Group

```typescript theme={null}
// Average rating by product
const avgRatingByProduct = await client.queryBuilder()
  .collection('reviews')
  .groupBy('productId')
  .avgBy('rating');
// Returns: { "prod_1": 4.5, "prod_2": 3.8 }
```

### Max/Min by Group

```typescript theme={null}
const maxPriceByCategory = await client.queryBuilder()
  .collection('products')
  .groupBy('category')
  .maxBy('price');
// Returns: { "electronics": 999, "books": 49 }
```

## Nested Field Grouping

GroupBy supports nested field paths:

```typescript theme={null}
// Group by nested field
const ordersByRegion = await client.queryBuilder()
  .collection('orders')
  .groupBy('customer.address.region')
  .sumBy('total');
// Returns: { "West": 10000, "East": 8500 }
```

## Combining Aggregations with Filters

```typescript theme={null}
// Sum revenue for completed orders in Q4 2024
const q4Revenue = await client.queryBuilder()
  .collection('orders')
  .find(b => [
    b.field('status').equals('completed'),
    b.field('createdAt').greaterThanOrEqual('2024-10-01'),
    b.field('createdAt').lessThanOrEqual('2024-12-31'),
  ])
  .sumBy('amount');

// Average rating for verified reviews only
const verifiedAvg = await client.queryBuilder()
  .collection('reviews')
  .whereField('verified').isTrue()
  .avgBy('rating');
```

## Aggregation Operators Reference

| Method                 | Description               | Return Type |
| ---------------------- | ------------------------- | ----------- |
| `count()`              | Count matching records    | `number`    |
| `sumBy(field)`         | Sum values of a field     | `number`    |
| `avgBy(field)`         | Average values of a field | `number`    |
| `minBy(field)`         | Find minimum value        | `T \| null` |
| `maxBy(field)`         | Find maximum value        | `T \| null` |
| `distinctBy(field)`    | Get unique values         | `string[]`  |
| `countDistinct(field)` | Count unique values       | `number`    |

## Analytics Dashboard Example

```typescript theme={null}
// Dashboard metrics for an e-commerce app
async function getDashboardMetrics() {
  const [
    totalOrders,
    totalRevenue,
    avgOrderValue,
    ordersByStatus,
    revenueByCategory
  ] = await Promise.all([
    client.queryBuilder()
      .collection('orders')
      .count(),

    client.queryBuilder()
      .collection('orders')
      .whereField('status').equals('completed')
      .sumBy('total'),

    client.queryBuilder()
      .collection('orders')
      .whereField('status').equals('completed')
      .avgBy('total'),

    client.queryBuilder()
      .collection('orders')
      .groupBy('status')
      .count(),

    client.queryBuilder()
      .collection('orders')
      .whereField('status').equals('completed')
      .groupBy('category')
      .sumBy('total')
  ]);

  return {
    totalOrders,
    totalRevenue,
    avgOrderValue,
    ordersByStatus,
    revenueByCategory
  };
}
```

## Next Steps

<CardGroup cols={2}>
  <Card title="JOINs" icon="link" href="/querying/joins">
    Relational queries with joinOne and joinMany
  </Card>

  <Card title="Materialized Views" icon="eye" href="/querying/materialized-views">
    Pre-computed views for complex queries
  </Card>
</CardGroup>