Data Service Aggregations

Powerful SQL-like aggregation queries with GROUP BY, HAVING, and aggregate functions for analytics and reporting.

Overview

Aggregations allow you to compute summary statistics and analytics directly in the database without fetching all records. This is essential for dashboards, reports, and analytics features.

Key Benefits:

Performance: Compute aggregates in database, not application
Efficiency: Return summarized data instead of thousands of rows
Power: SQL-like capabilities (SUM, AVG, COUNT, GROUP BY, HAVING)
Flexibility: Combine with filters, sorting, and pagination

Base URL: Same as data endpoints: /api/apps/{app_slug}/datatables/{name}/data/

Aggregate Functions

Count Functions

Count all records:

GET /data/?_aggregate=count(*)

Response:

{
  "data": [],
  "aggregates": {
    "count": 150
  }
}

Count non-null values:

GET /data/?_aggregate=count(email)

Math Functions

Sum:

GET /data/?_aggregate=sum(price)

Average:

GET /data/?_aggregate=avg(rating)

Min/Max:

GET /data/?_aggregate=min(created_at),max(created_at)

Response:

{
  "aggregates": {
    "sum_price": 15450.50,
    "avg_rating": 4.2,
    "min_created_at": "2024-01-01T00:00:00Z",
    "max_created_at": "2024-12-30T23:59:59Z"
  }
}

Array Functions

Array aggregation:

GET /data/?_aggregate=array_agg(tags)

String aggregation:

GET /data/?_aggregate=string_agg(name)

JSON aggregation:

GET /data/?_aggregate=json_agg(metadata)

Response:

{
  "aggregates": {
    "array_agg_tags": ["python", "django", "api", "tutorial"],
    "string_agg_name": "Alice, Bob, Carol",
    "json_agg_metadata": [
      {"version": "1.0", "author": "alice"},
      {"version": "2.0", "author": "bob"}
    ]
  }
}

Statistical Functions

Standard deviation:

GET /data/?_aggregate=stddev(price)

Variance:

GET /data/?_aggregate=variance(score)

Multiple Aggregates

Combine multiple aggregate functions:

GET /data/?_aggregate=count(*),sum(price),avg(price),min(price),max(price)

Response:

{
  "aggregates": {
    "count": 50,
    "sum_price": 5000.00,
    "avg_price": 100.00,
    "min_price": 10.00,
    "max_price": 500.00
  }
}

GROUP BY

Group results by one or more fields to get per-group aggregates.

Single Field Grouping

Count posts by category:

GET /data/?_aggregate=count(*)&_group_by=category

Response:

{
  "data": [
    {"category": "tutorial", "count": 25},
    {"category": "announcement", "count": 10},
    {"category": "discussion", "count": 15}
  ],
  "total": 3
}

Sum sales by product:

GET /data/?_aggregate=sum(amount),count(*)&_group_by=product_id

Response:

{
  "data": [
    {"product_id": 1, "sum_amount": 15000, "count": 150},
    {"product_id": 2, "sum_amount": 8500, "count": 85},
    {"product_id": 3, "sum_amount": 12000, "count": 120}
  ]
}

Multiple Field Grouping

Group by multiple dimensions:

GET /data/?_aggregate=count(*),avg(rating)&_group_by=category,status

Response:

{
  "data": [
    {"category": "electronics", "status": "active", "count": 50, "avg_rating": 4.5},
    {"category": "electronics", "status": "inactive", "count": 10, "avg_rating": 3.8},
    {"category": "books", "status": "active", "count": 100, "avg_rating": 4.2}
  ]
}

Date/Time Grouping

Group by date:

GET /data/?_aggregate=count(*)&_group_by=date_trunc('day',created_at)

Group by month:

GET /data/?_aggregate=sum(revenue)&_group_by=date_trunc('month',created_at)

Group by hour:

GET /data/?_aggregate=count(*)&_group_by=date_trunc('hour',created_at)

Response:

{
  "data": [
    {"date_trunc": "2024-01-01", "count": 150},
    {"date_trunc": "2024-01-02", "count": 200},
    {"date_trunc": "2024-01-03", "count": 180}
  ]
}

HAVING Clause

Filter aggregated results (WHERE filters before aggregation, HAVING filters after).

Basic HAVING

Categories with more than 10 posts:

GET /data/?_aggregate=count(*)&_group_by=category&_having=count__gt=10

Products with total sales over $10,000:

GET /data/?_aggregate=sum(amount)&_group_by=product_id&_having=sum_amount__gte=10000

Multiple HAVING Conditions

Active products with >50 orders and avg rating >4.0:

GET /data/?status=active&_aggregate=count(*),avg(rating)&_group_by=product_id&_having=count__gt=50,avg_rating__gte=4.0

HAVING Operators

All filter operators work with HAVING:

Operator	Example	Meaning
`__gt`	`count__gt=10`	Greater than
`__gte`	`sum_amount__gte=1000`	Greater than or equal
`__lt`	`avg_rating__lt=3.0`	Less than
`__lte`	`count__lte=100`	Less than or equal
`__eq`	`count__eq=50`	Equal to
`__ne`	`count__ne=0`	Not equal to

Complex Examples

Sales Analytics Dashboard

Monthly revenue with order statistics:

GET /api/apps/ecommerce/datatables/orders/data/?_aggregate=sum(total),count(*),avg(total)&_group_by=date_trunc('month',created_at)&_having=sum_total__gte=10000&ordering=-date_trunc

Response:

{
  "data": [
    {
      "date_trunc": "2024-03-01",
      "sum_total": 45000.00,
      "count": 450,
      "avg_total": 100.00
    },
    {
      "date_trunc": "2024-02-01",
      "sum_total": 38000.00,
      "count": 380,
      "avg_total": 100.00
    }
  ]
}

User Activity Report

Activity by user and day:

GET /api/apps/analytics/datatables/events/data/?_aggregate=count(*),json_agg(action_type)&_group_by=user_id,date_trunc('day',created_at)&created_at__gte=2024-01-01

Response:

{
  "data": [
    {
      "user_id": 1,
      "date_trunc": "2024-01-15",
      "count": 45,
      "json_agg_action_type": ["click", "view", "download", "share"]
    }
  ]
}

Inventory Summary

Low stock products by warehouse:

GET /api/apps/inventory/datatables/stock/data/?_aggregate=sum(quantity),array_agg(product_name)&_group_by=warehouse_id&_having=sum_quantity__lt=100

Response:

{
  "data": [
    {
      "warehouse_id": 5,
      "sum_quantity": 45,
      "array_agg_product_name": ["Widget A", "Widget B", "Gadget C"]
    }
  ]
}

Customer Segmentation

High-value customers:

GET /api/apps/crm/datatables/orders/data/?_aggregate=count(*),sum(total),avg(total)&_group_by=customer_id&_having=sum_total__gte=5000,count__gte=10

Product Performance

Top-rated products by category:

GET /api/apps/shop/datatables/products/data/?_aggregate=avg(rating),count(reviews)&_group_by=category&_having=avg_rating__gte=4.5,count_reviews__gte=50&ordering=-avg_rating

Advanced Features

SQL Expressions with Aliases

Use custom SQL expressions with alias names:

Average days between start and end:

GET /data/?_aggregate=avg(extract(epoch from (end_date - start_date)) / 86400) as avg_days&_group_by=project_id

Percentage calculations:

GET /data/?_aggregate=sum(case when status='completed' then 1 else 0 end) * 100.0 / count(*) as completion_rate&_group_by=category

Combining with Filters

Apply WHERE filters before aggregation:

Published posts only, grouped by author:

GET /data/?status=published&_aggregate=count(*),avg(views)&_group_by=author_id

Sales this month, by product:

GET /data/?created_at__gte=2024-03-01&_aggregate=sum(amount)&_group_by=product_id

Combining with Populate

Aggregate on joined data:

Average post rating per author (with author names):

GET /data/?_aggregate=avg(rating),count(*)&_group_by=author_id&populate=author

Response includes populated author data:

{
  "data": [
    {
      "author_id": 1,
      "author": {"id": 1, "name": "Alice", "email": "[email protected]"},
      "avg_rating": 4.5,
      "count": 25
    }
  ]
}

Timezone Handling

Group by date in specific timezone:

GET /data/?_aggregate=count(*)&_group_by=date_trunc('day',created_at AT TIME ZONE 'America/New_York')

Nested Aggregations

Count of distinct values:

GET /data/?_aggregate=count(distinct category)

Count users per country, then count countries:

GET /data/?_aggregate=count(*)&_group_by=country
# Then aggregate the aggregated result

Response Format

Without GROUP BY

{
  "data": [],
  "aggregates": {
    "count": 150,
    "sum_price": 15000,
    "avg_rating": 4.2
  }
}

With GROUP BY

{
  "data": [
    {
      "category": "books",
      "sum_price": 5000,
      "count": 50,
      "avg_price": 100
    },
    {
      "category": "electronics",
      "sum_price": 10000,
      "count": 100,
      "avg_price": 100
    }
  ],
  "total": 2
}

Aggregate Naming Convention

Aggregate results are named: {function}_{field} or custom alias:

count(*) → count
sum(price) → sum_price
avg(rating) → avg_rating
max(created_at) → max_created_at
Custom: sum(price) as total_revenue → total_revenue

Performance Tips

1. Index GROUP BY Columns

-- Add index on frequently grouped columns
CREATE INDEX idx_posts_category ON posts(category);
CREATE INDEX idx_orders_created_at ON orders(created_at);

See Indexes Guide for index management.

2. Use HAVING to Limit Results

# ✅ Good - Reduce result set with HAVING
GET /data/?_aggregate=count(*)&_group_by=category&_having=count__gte=10

# ❌ Less efficient - Fetch all groups then filter
GET /data/?_aggregate=count(*)&_group_by=category

3. Avoid Aggregating on Text Fields

# ❌ Slow - Aggregating on TEXT column
GET /data/?_aggregate=sum(description)

# ✅ Better - Aggregate on numeric/date columns
GET /data/?_aggregate=sum(word_count)

4. Use Partial Indexes

For filtered aggregations, create partial indexes:

CREATE INDEX idx_active_products ON products(category) WHERE status = 'active';

5. Limit Date Ranges

# ✅ Good - Limit date range
GET /data/?created_at__gte=2024-01-01&_aggregate=count(*)&_group_by=date_trunc('day',created_at)

# ❌ Slow - Aggregate all historical data
GET /data/?_aggregate=count(*)&_group_by=date_trunc('day',created_at)

6. Pagination on Aggregated Results

Large aggregated result sets can be paginated:

GET /data/?_aggregate=count(*)&_group_by=product_id&page=1&page_size=50

Common Use Cases

Dashboard Widgets

Total sales today:

GET /data/?created_at__gte=2024-03-15&_aggregate=sum(total),count(*)

Active users this week:

GET /data/?last_seen__gte=2024-03-08&_aggregate=count(distinct user_id)

Average response time:

GET /data/?_aggregate=avg(response_time_ms)

Analytics Reports

User growth over time:

GET /data/?_aggregate=count(*)&_group_by=date_trunc('month',created_at)&ordering=date_trunc

Revenue by product category:

GET /data/?_aggregate=sum(amount),count(*)&_group_by=category&ordering=-sum_amount

Conversion funnel:

GET /data/?_aggregate=count(*)&_group_by=funnel_stage&ordering=stage_order

Business Intelligence

Customer lifetime value:

GET /data/?_aggregate=sum(order_total),count(*),avg(order_total)&_group_by=customer_id&_having=count__gte=3

Churn analysis:

GET /data/?_aggregate=count(*)&_group_by=date_trunc('month',last_order_date)

Inventory turnover:

GET /data/?_aggregate=sum(quantity_sold),sum(quantity_remaining)&_group_by=product_id,warehouse_id

Limitations

Aggregation Complexity

Very complex aggregations may hit query timeouts. Break into simpler queries or use materialized views for pre-computed results.

Nested Aggregations

Currently limited support for nested aggregations (aggregating aggregated results). Use multiple queries or compute client-side.

Window Functions

Window functions (OVER, PARTITION BY, ROW_NUMBER) are not yet supported via query parameters. Use custom SQL endpoints if needed.

Client Examples

JavaScript

// Sales by month
const salesData = await fetch(
  `/api/apps/shop/datatables/orders/data/?` +
  `_aggregate=sum(total),count(*)` +
  `&_group_by=date_trunc('month',created_at)` +
  `&_having=sum_total__gte=1000` +
  `&ordering=-date_trunc`,
  {
    headers: { Authorization: 'Bearer YOUR_TOKEN' }
  }
).then(r => r.json());

// Chart the results
const labels = salesData.data.map(row => row.date_trunc);
const values = salesData.data.map(row => row.sum_total);

// Use with Chart.js, etc.
new Chart(ctx, {
  type: 'bar',
  data: {
    labels: labels,
    datasets: [{
      label: 'Monthly Sales',
      data: values
    }]
  }
});

Python

import requests
from datetime import datetime, timedelta

# Top products by sales
response = requests.get(
    'https://api.example.com/api/apps/shop/datatables/orders/data/',
    headers={'Authorization': 'Bearer YOUR_TOKEN'},
    params={
        '_aggregate': 'sum(amount),count(*)',
        '_group_by': 'product_id',
        '_having': 'sum_amount__gte=5000',
        'ordering': '-sum_amount',
        'page_size': 10,
        'populate': 'product'
    }
)

data = response.json()['data']
for item in data:
    product = item['product']['name']
    revenue = item['sum_amount']
    orders = item['count']
    print(f"{product}: ${revenue:,.2f} ({orders} orders)")

CRUD Operations - Creating, reading, updating, deleting data
Querying Guide - Filter operators and query syntax
Indexes - Creating indexes for better aggregation performance
API Reference - Complete endpoint listing
Schema Guide - Defining field types for aggregations

Overview​

Aggregate Functions​

Count Functions​

Math Functions​

Array Functions​

Statistical Functions​

Multiple Aggregates​

GROUP BY​

Single Field Grouping​

Multiple Field Grouping​

Date/Time Grouping​

HAVING Clause​

Basic HAVING​

Multiple HAVING Conditions​

HAVING Operators​

Complex Examples​

Sales Analytics Dashboard​

User Activity Report​

Inventory Summary​

Customer Segmentation​

Product Performance​

Advanced Features​

SQL Expressions with Aliases​

Combining with Filters​

Combining with Populate​

Timezone Handling​

Nested Aggregations​

Response Format​

Without GROUP BY​

With GROUP BY​

Aggregate Naming Convention​

Performance Tips​

1. Index GROUP BY Columns​

2. Use HAVING to Limit Results​

3. Avoid Aggregating on Text Fields​

4. Use Partial Indexes​

5. Limit Date Ranges​

6. Pagination on Aggregated Results​

Common Use Cases​

Dashboard Widgets​

Analytics Reports​

Business Intelligence​

Limitations​

Aggregation Complexity​

Nested Aggregations​

Window Functions​

Client Examples​

JavaScript​

Python​

Related Documentation​

Overview

Aggregate Functions

Count Functions

Math Functions

Array Functions

Statistical Functions

Multiple Aggregates

GROUP BY

Single Field Grouping

Multiple Field Grouping

Date/Time Grouping

HAVING Clause

Basic HAVING

Multiple HAVING Conditions

HAVING Operators

Complex Examples

Sales Analytics Dashboard

User Activity Report

Inventory Summary

Customer Segmentation

Product Performance

Advanced Features

SQL Expressions with Aliases

Combining with Filters

Combining with Populate

Timezone Handling

Nested Aggregations

Response Format

Without GROUP BY

With GROUP BY

Aggregate Naming Convention

Performance Tips

1. Index GROUP BY Columns

2. Use HAVING to Limit Results

3. Avoid Aggregating on Text Fields

4. Use Partial Indexes

5. Limit Date Ranges

6. Pagination on Aggregated Results

Common Use Cases

Dashboard Widgets

Analytics Reports

Business Intelligence

Limitations

Aggregation Complexity

Nested Aggregations

Window Functions

Client Examples

JavaScript

Python

Related Documentation