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:

REST API
Python
JavaScript

GET /data/?_aggregate=count(*)

Response:

{
  "status": "success",
  "message": "Aggregation completed successfully",
  "data": [],
  "aggregates": {
    "count": 150
  }
}

result = auth_client.database.from_("my_table").aggregate("count(*)").execute()

const result = await database.from("my_table")
  .aggregate("count(*)")
  .execute()

Count non-null values:

REST API
Python
JavaScript

GET /data/?_aggregate=count(email)

result = auth_client.database.from_("my_table").aggregate("count(email)").execute()

const result = await database.from("my_table")
  .aggregate("count(email)")
  .execute()

Math Functions

Sum:

REST API
Python
JavaScript

GET /data/?_aggregate=sum(price)

result = auth_client.database.from_("my_table").aggregate("sum(price)").execute()

const result = await database.from("my_table")
  .aggregate("sum(price)")
  .execute()

Average:

REST API
Python
JavaScript

GET /data/?_aggregate=avg(rating)

result = auth_client.database.from_("my_table").aggregate("avg(rating)").execute()

const result = await database.from("my_table")
  .aggregate("avg(rating)")
  .execute()

Min/Max:

REST API
Python
JavaScript

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

Response:

{
  "status": "success",
  "message": "Aggregation completed successfully",
  "data": [],
  "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"
  }
}

result = auth_client.database.from_("my_table").aggregate("min(created_at),max(created_at)").execute()

const result = await database.from("my_table")
  .aggregate("min(created_at),max(created_at)")
  .execute()

Array Functions

Array aggregation:

REST API
Python
JavaScript

GET /data/?_aggregate=array_agg(tags)

result = auth_client.database.from_("my_table").aggregate("array_agg(tags)").execute()

const result = await database.from("my_table")
  .aggregate("array_agg(tags)")
  .execute()

String aggregation:

REST API
Python
JavaScript

GET /data/?_aggregate=string_agg(name)

result = auth_client.database.from_("my_table").aggregate("string_agg(name)").execute()

const result = await database.from("my_table")
  .aggregate("string_agg(name)")
  .execute()

JSON aggregation:

REST API
Python
JavaScript

GET /data/?_aggregate=json_agg(metadata)

Response:

{
  "status": "success",
  "message": "Aggregation completed successfully",
  "data": [],
  "aggregates": {
    "array_agg_tags": ["beginner", "intermediate", "api", "tutorial"],
    "string_agg_name": "Alice, Bob, Carol",
    "json_agg_metadata": [
      {"version": "1.0", "author": "alice"},
      {"version": "2.0", "author": "bob"}
    ]
  }
}

result = auth_client.database.from_("my_table").aggregate("json_agg(metadata)").execute()

const result = await database.from("my_table")
  .aggregate("json_agg(metadata)")
  .execute()

Statistical Functions

Standard deviation:

REST API
Python
JavaScript

GET /data/?_aggregate=stddev(price)

result = auth_client.database.from_("my_table").aggregate("stddev(price)").execute()

const result = await database.from("my_table")
  .aggregate("stddev(price)")
  .execute()

Variance:

REST API
Python
JavaScript

GET /data/?_aggregate=variance(score)

result = auth_client.database.from_("my_table").aggregate("variance(score)").execute()

const result = await database.from("my_table")
  .aggregate("variance(score)")
  .execute()

Multiple Aggregates

Combine multiple aggregate functions:

REST API
Python
JavaScript

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
  }
}

result = (
    auth_client.database.from_("my_table")
    .aggregate("count(*),sum(price),avg(price),min(price),max(price)")
    .execute()
)

const result = await database.from("my_table")
  .aggregate("count(*),sum(price),avg(price),min(price),max(price)")
  .execute()

GROUP BY

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

Single Field Grouping

Count posts by category:

REST API
Python
JavaScript

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

Response:

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

result = (
    auth_client.database.from_("posts")
    .aggregate("count(*)")
    .group_by("category")
    .execute()
)

const result = await database.from("posts")
  .aggregate("count(*)")
  .groupBy("category")
  .execute()

Sum sales by product:

REST API
Python
JavaScript

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}
  ]
}

result = (
    auth_client.database.from_("sales")
    .aggregate("sum(amount),count(*)")
    .group_by("product_id")
    .execute()
)

const result = await database.from("sales")
  .aggregate("sum(amount),count(*)")
  .groupBy("product_id")
  .execute()

Multiple Field Grouping

Group by multiple dimensions:

REST API
Python
JavaScript

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}
  ]
}

result = (
    auth_client.database.from_("products")
    .aggregate("count(*),avg(rating)")
    .group_by("category,status")
    .execute()
)

const result = await database.from("products")
  .aggregate("count(*),avg(rating)")
  .groupBy("category,status")
  .execute()

Date/Time Grouping

Group by date:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("events")
    .aggregate("count(*)")
    .group_by("date_trunc('day',created_at)")
    .execute()
)

const result = await database.from("events")
  .aggregate("count(*)")
  .groupBy("date_trunc('day',created_at)")
  .execute()

Group by month:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("revenue")
    .aggregate("sum(revenue)")
    .group_by("date_trunc('month',created_at)")
    .execute()
)

const result = await database.from("revenue")
  .aggregate("sum(revenue)")
  .groupBy("date_trunc('month',created_at)")
  .execute()

Group by hour:

REST API
Python
JavaScript

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}
  ]
}

result = (
    auth_client.database.from_("events")
    .aggregate("count(*)")
    .group_by("date_trunc('hour',created_at)")
    .execute()
)

const result = await database.from("events")
  .aggregate("count(*)")
  .groupBy("date_trunc('hour',created_at)")
  .execute()

HAVING Clause

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

Basic HAVING

Categories with more than 10 posts:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("posts")
    .aggregate("count(*)")
    .group_by("category")
    .having("count__gt=10")
    .execute()
)

const result = await database.from("posts")
  .aggregate("count(*)")
  .groupBy("category")
  .having("count__gt=10")
  .execute()

Products with total sales over $10,000:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .aggregate("sum(amount)")
    .group_by("product_id")
    .having("sum_amount__gte=10000")
    .execute()
)

const result = await database.from("orders")
  .aggregate("sum(amount)")
  .groupBy("product_id")
  .having("sum_amount__gte=10000")
  .execute()

Multiple HAVING Conditions

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

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("products")
    .filter("status", "eq", "active")
    .aggregate("count(*),avg(rating)")
    .group_by("product_id")
    .having("count__gt=50,avg_rating__gte=4.0")
    .execute()
)

const result = await database.from("products")
  .filter("status", "eq", "active")
  .aggregate("count(*),avg(rating)")
  .groupBy("product_id")
  .having("count__gt=50,avg_rating__gte=4.0")
  .execute()

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:

REST API
Python
JavaScript

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
    }
  ]
}

result = (
    auth_client.database.from_("orders")
    .aggregate("sum(total),count(*),avg(total)")
    .group_by("date_trunc('month',created_at)")
    .having("sum_total__gte=10000")
    .sort("-date_trunc")
    .execute()
)

const result = await database.from("orders")
  .aggregate("sum(total),count(*),avg(total)")
  .groupBy("date_trunc('month',created_at)")
  .having("sum_total__gte=10000")
  .sort("-date_trunc")
  .execute()

User Activity Report

Activity by user and day:

REST API
Python
JavaScript

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"]
    }
  ]
}

result = (
    auth_client.database.from_("events")
    .aggregate("count(*),json_agg(action_type)")
    .group_by("user_id,date_trunc('day',created_at)")
    .filter("created_at", "gte", "2024-01-01")
    .execute()
)

const result = await database.from("events")
  .aggregate("count(*),json_agg(action_type)")
  .groupBy("user_id,date_trunc('day',created_at)")
  .filter("created_at", "gte", "2024-01-01")
  .execute()

Inventory Summary

Low stock products by warehouse:

REST API
Python
JavaScript

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"]
    }
  ]
}

result = (
    auth_client.database.from_("stock")
    .aggregate("sum(quantity),array_agg(product_name)")
    .group_by("warehouse_id")
    .having("sum_quantity__lt=100")
    .execute()
)

const result = await database.from("stock")
  .aggregate("sum(quantity),array_agg(product_name)")
  .groupBy("warehouse_id")
  .having("sum_quantity__lt=100")
  .execute()

Customer Segmentation

High-value customers:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .aggregate("count(*),sum(total),avg(total)")
    .group_by("customer_id")
    .having("sum_total__gte=5000,count__gte=10")
    .execute()
)

const result = await database.from("orders")
  .aggregate("count(*),sum(total),avg(total)")
  .groupBy("customer_id")
  .having("sum_total__gte=5000,count__gte=10")
  .execute()

Product Performance

Top-rated products by category:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("products")
    .aggregate("avg(rating),count(reviews)")
    .group_by("category")
    .having("avg_rating__gte=4.5,count_reviews__gte=50")
    .sort("-avg_rating")
    .execute()
)

const result = await database.from("products")
  .aggregate("avg(rating),count(reviews)")
  .groupBy("category")
  .having("avg_rating__gte=4.5,count_reviews__gte=50")
  .sort("-avg_rating")
  .execute()

Advanced Features

SQL Expressions with Aliases

Use custom SQL expressions with alias names:

Average days between start and end:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("projects")
    .aggregate("avg(extract(epoch from (end_date - start_date)) / 86400) as avg_days")
    .group_by("project_id")
    .execute()
)

const result = await database.from("projects")
  .aggregate("avg(extract(epoch from (end_date - start_date)) / 86400) as avg_days")
  .groupBy("project_id")
  .execute()

Percentage calculations:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("tasks")
    .aggregate("sum(case when status='completed' then 1 else 0 end) * 100.0 / count(*) as completion_rate")
    .group_by("category")
    .execute()
)

const result = await database.from("tasks")
  .aggregate("sum(case when status='completed' then 1 else 0 end) * 100.0 / count(*) as completion_rate")
  .groupBy("category")
  .execute()

Combining with Filters

Apply WHERE filters before aggregation:

Published posts only, grouped by author:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("posts")
    .filter("status", "eq", "published")
    .aggregate("count(*),avg(views)")
    .group_by("author_id")
    .execute()
)

const result = await database.from("posts")
  .filter("status", "eq", "published")
  .aggregate("count(*),avg(views)")
  .groupBy("author_id")
  .execute()

Sales this month, by product:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("sales")
    .filter("created_at", "gte", "2024-03-01")
    .aggregate("sum(amount)")
    .group_by("product_id")
    .execute()
)

const result = await database.from("sales")
  .filter("created_at", "gte", "2024-03-01")
  .aggregate("sum(amount)")
  .groupBy("product_id")
  .execute()

Combining with Populate

Aggregate on joined data:

Average post rating per author (with author names):

REST API
Python
JavaScript

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": "alice@example.com"},
      "avg_rating": 4.5,
      "count": 25
    }
  ]
}

result = (
    auth_client.database.from_("posts")
    .aggregate("avg(rating),count(*)")
    .group_by("author_id")
    .populate("author")
    .execute()
)

const result = await database.from("posts")
  .aggregate("avg(rating),count(*)")
  .groupBy("author_id")
  .populate("author")
  .execute()

Timezone Handling

Group by date in specific timezone:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("events")
    .aggregate("count(*)")
    .group_by("date_trunc('day',created_at AT TIME ZONE 'America/New_York')")
    .execute()
)

const result = await database.from("events")
  .aggregate("count(*)")
  .groupBy("date_trunc('day',created_at AT TIME ZONE 'America/New_York')")
  .execute()

Nested Aggregations

Count of distinct values:

REST API
Python
JavaScript

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

result = auth_client.database.from_("products").aggregate("count(distinct category)").execute()

const result = await database.from("products")
  .aggregate("count(distinct category)")
  .execute()

Count users per country, then count countries:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("users")
    .aggregate("count(*)")
    .group_by("country")
    .execute()
)
# Then aggregate the aggregated result client-side

const result = await database.from("users")
  .aggregate("count(*)")
  .groupBy("country")
  .execute()
// Then aggregate the aggregated result client-side

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:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .aggregate("count(*)")
    .group_by("product_id")
    .page_size(50)
    .page(1)
    .execute()
)

const result = await database.from("orders")
  .aggregate("count(*)")
  .groupBy("product_id")
  .limit(50)
  .offset(0)
  .execute()

Common Use Cases

Dashboard Widgets

Total sales today:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .filter("created_at", "gte", "2024-03-15")
    .aggregate("sum(total),count(*)")
    .execute()
)

const result = await database.from("orders")
  .filter("created_at", "gte", "2024-03-15")
  .aggregate("sum(total),count(*)")
  .execute()

Active users this week:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("sessions")
    .filter("last_seen", "gte", "2024-03-08")
    .aggregate("count(distinct user_id)")
    .execute()
)

const result = await database.from("sessions")
  .filter("last_seen", "gte", "2024-03-08")
  .aggregate("count(distinct user_id)")
  .execute()

Average response time:

REST API
Python
JavaScript

GET /data/?_aggregate=avg(response_time_ms)

result = auth_client.database.from_("requests").aggregate("avg(response_time_ms)").execute()

const result = await database.from("requests")
  .aggregate("avg(response_time_ms)")
  .execute()

Analytics Reports

User growth over time:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("users")
    .aggregate("count(*)")
    .group_by("date_trunc('month',created_at)")
    .sort("date_trunc")
    .execute()
)

const result = await database.from("users")
  .aggregate("count(*)")
  .groupBy("date_trunc('month',created_at)")
  .sort("date_trunc")
  .execute()

Revenue by product category:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .aggregate("sum(amount),count(*)")
    .group_by("category")
    .sort("-sum_amount")
    .execute()
)

const result = await database.from("orders")
  .aggregate("sum(amount),count(*)")
  .groupBy("category")
  .sort("-sum_amount")
  .execute()

Conversion funnel:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("funnel_events")
    .aggregate("count(*)")
    .group_by("funnel_stage")
    .sort("stage_order")
    .execute()
)

const result = await database.from("funnel_events")
  .aggregate("count(*)")
  .groupBy("funnel_stage")
  .sort("stage_order")
  .execute()

Business Intelligence

Customer lifetime value:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("orders")
    .aggregate("sum(order_total),count(*),avg(order_total)")
    .group_by("customer_id")
    .having("count__gte=3")
    .execute()
)

const result = await database.from("orders")
  .aggregate("sum(order_total),count(*),avg(order_total)")
  .groupBy("customer_id")
  .having("count__gte=3")
  .execute()

Churn analysis:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("customers")
    .aggregate("count(*)")
    .group_by("date_trunc('month',last_order_date)")
    .execute()
)

const result = await database.from("customers")
  .aggregate("count(*)")
  .groupBy("date_trunc('month',last_order_date)")
  .execute()

Inventory turnover:

REST API
Python
JavaScript

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

result = (
    auth_client.database.from_("inventory")
    .aggregate("sum(quantity_sold),sum(quantity_remaining)")
    .group_by("product_id,warehouse_id")
    .execute()
)

const result = await database.from("inventory")
  .aggregate("sum(quantity_sold),sum(quantity_remaining)")
  .groupBy("product_id,warehouse_id")
  .execute()

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

Sales by Month (Full Example)

REST API
Python
JavaScript

curl -X GET "https://your-site.taruvi.cloud/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" \
  -H "Authorization: Bearer YOUR_TOKEN"

from taruvi import Client

client = Client(api_url="https://your-site.taruvi.cloud", app_slug="shop")
auth_client = client.auth.signInWithPassword(username="user@example.com", password="pass")

# Sales by month
result = (
    auth_client.database.from_("orders")
    .aggregate("sum(total),count(*)")
    .group_by("date_trunc('month',created_at)")
    .having("sum_total__gte=1000")
    .sort("-date_trunc")
    .execute()
)

for row in result["data"]:
    print(f"{row['date_trunc']}: ${row['sum_total']:,.2f} ({row['count']} orders)")

import { createClient } from "@taruvi/sdk"

const client = createClient({ url: "https://your-site.taruvi.cloud", appSlug: "shop" })
const { database } = await client.auth.signInWithPassword({
  username: "user@example.com",
  password: "pass",
})

// Sales by month
const result = await database.from("orders")
  .aggregate("sum(total),count(*)")
  .groupBy("date_trunc('month',created_at)")
  .having("sum_total__gte=1000")
  .sort("-date_trunc")
  .execute()

// Chart the results
const labels = result.data.map(row => row.date_trunc)
const values = result.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 }],
  },
})

Top Products by Sales (Full Example)

REST API
Python
JavaScript

curl -X GET "https://your-site.taruvi.cloud/api/apps/shop/datatables/orders/data/?_aggregate=sum(amount),count(*)&_group_by=product_id&_having=sum_amount__gte=5000&ordering=-sum_amount&page_size=10&populate=product" \
  -H "Authorization: Bearer YOUR_TOKEN"

from taruvi import Client

client = Client(api_url="https://your-site.taruvi.cloud", app_slug="shop")
auth_client = client.auth.signInWithPassword(username="user@example.com", password="pass")

# Top products by sales
result = (
    auth_client.database.from_("orders")
    .aggregate("sum(amount),count(*)")
    .group_by("product_id")
    .having("sum_amount__gte=5000")
    .sort("-sum_amount")
    .page_size(10)
    .populate("product")
    .execute()
)

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

import { createClient } from "@taruvi/sdk"

const client = createClient({ url: "https://your-site.taruvi.cloud", appSlug: "shop" })
const { database } = await client.auth.signInWithPassword({
  username: "user@example.com",
  password: "pass",
})

// Top products by sales
const result = await database.from("orders")
  .aggregate("sum(amount),count(*)")
  .groupBy("product_id")
  .having("sum_amount__gte=5000")
  .sort("-sum_amount")
  .limit(10)
  .populate("product")
  .execute()

for (const item of result.data) {
  console.log(`${item.product.name}: $${item.sum_amount.toFixed(2)} (${item.count} 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​

Sales by Month (Full Example)​

Top Products by Sales (Full Example)​

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

Sales by Month (Full Example)

Top Products by Sales (Full Example)

Related Documentation