Aggregate API - Statistical Queries

v1.2.0+

The aggregate API is Lovrabet SDK's data aggregation and statistics interface. It supports SUM, COUNT, AVG aggregate functions combined with GROUP BY and HAVING for complex data analysis - a core tool for building reports and dashboards.

Prerequisites

Before starting, please ensure you have completed SDK Configuration. It is recommended to use CLI auto-generated configuration.

The following examples use the alias method client.models.orders for readability. You can also use the standard method client.models.dataset_xxx (functionally identical).

Operation Limit

aggregate() operation only supports WebAPI mode (Cookie authentication). OpenAPI mode does not support this feature.

🎯 Main Features

• ✅ Aggregate Functions - Supports SUM, COUNT, AVG
• ✅ Group Statistics - GROUP BY aggregation
• ✅ Conditional Filtering - WHERE pre-filter + HAVING post-filter
• ✅ Result Sorting - Sort by aggregated results
• ✅ Pagination - Pagination for large grouped results
• ✅ Precision Control - Rounding and decimal places

📝 API Signature

async aggregate<T = any>(params: AggregateParams): Promise<T | T[]>

Parameter Description

interface AggregateParams {
  select?: string[];              // Fields to return (usually for GROUP BY)
  aggregate: AggregateField[];    // Aggregate configuration (required)
  where?: WhereCondition;         // Query conditions (pre-aggregation filter)
  groupBy?: string[];             // Grouping fields
  having?: HavingCondition[];     // Post-grouping filter conditions
  orderBy?: SortList;             // Sorting rules
  currentPage?: number;           // Current page number
  pageSize?: number;              // Records per page
}

Aggregate Field Configuration

interface AggregateField {
  type: 'SUM' | 'COUNT' | 'AVG';  // Aggregate type
  field: string;                  // Field name (use '*' for COUNT all)
  alias?: string;                 // Alias for the result field
  distinct?: boolean;             // Distinct count (COUNT DISTINCT)
  round?: boolean;                // Round the result
  precision?: number;             // Decimal places (with round)
}

Return Value

• No GROUP BY: Returns a single object { alias1: value1, alias2: value2 }
• With GROUP BY: Returns an array [{ groupField: value, alias1: value1 }, ...]

📊 Aggregate Functions

SUM

Calculates the sum of a numeric field.

// Calculate total order amount
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'SUM', field: 'total_amount', alias: 'total_sales' },
  ],
});

console.log(result);
// { total_sales: 125678.90 }

COUNT

Counts the number of records.

// Count total orders
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'total_count' },
  ],
});

console.log(result);
// { total_count: 1250 }

// Count unique customers
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'COUNT', field: 'customer_id', alias: 'unique_customers', distinct: true },
  ],
});

console.log(result);
// { unique_customers: 320 }

AVG

Calculates the average of a numeric field.

// Calculate average order amount
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'AVG', field: 'total_amount', alias: 'avg_amount', round: true, precision: 2 },
  ],
});

console.log(result);
// { avg_amount: 125.45 }

Multiple Aggregations

// Get count, sum, and average together
const stats = await client.models.orders.aggregate({
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'order_count' },
    { type: 'SUM', field: 'total_amount', alias: 'total_sales', round: true, precision: 2 },
    { type: 'AVG', field: 'total_amount', alias: 'avg_amount', round: true, precision: 2 },
  ],
});

console.log(stats);
// { order_count: 1250, total_sales: 156789.50, avg_amount: 125.43 }

🔀 GROUP BY

Basic Grouping

// Group orders by status
const result = await client.models.orders.aggregate({
  select: ['status'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'count' },
    { type: 'SUM', field: 'total_amount', alias: 'total' },
  ],
  groupBy: ['status'],
});

console.log(result);
// [
//   { status: 'pending', count: 45, total: 5678.00 },
//   { status: 'completed', count: 1200, total: 145678.50 },
//   { status: 'cancelled', count: 5, total: 433.00 },
// ]

Multi-Field Grouping

// Group by year and month
const result = await client.models.orders.aggregate({
  select: ['year', 'month'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'order_count' },
    { type: 'SUM', field: 'total_amount', alias: 'total_sales' },
  ],
  groupBy: ['year', 'month'],
  orderBy: [{ year: 'desc' }, { month: 'desc' }],
});

console.log(result);
// [
//   { year: 2025, month: 2, order_count: 150, total_sales: 18765.00 },
//   { year: 2025, month: 1, order_count: 200, total_sales: 25432.00 },
// ]

🔍 WHERE Filtering

Filter data before aggregation.

// Statistics for completed orders only
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'completed_count' },
    { type: 'SUM', field: 'total_amount', alias: 'completed_total' },
  ],
  where: {
    status: { $eq: 'completed' },
  },
});

console.log(result);
// { completed_count: 1200, completed_total: 145678.50 }

// Filter by date range
const result = await client.models.orders.aggregate({
  select: ['status'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'count' },
    { type: 'SUM', field: 'total_amount', alias: 'total' },
  ],
  where: {
    create_time: { $gte: '2025-01-01', $lte: '2025-01-31' },
    status: { $in: ['completed', 'shipped'] },
  },
  groupBy: ['status'],
});

🎯 HAVING Filtering

HAVING filters aggregated results after grouping.

// Find categories with sales over 10000
const result = await client.models.orders.aggregate({
  select: ['category_id'],
  aggregate: [
    { type: 'SUM', field: 'total_amount', alias: 'total_sales' },
  ],
  groupBy: ['category_id'],
  having: [
    { columnName: 'total_sales', condition: { $gte: 10000 } },
  ],
  orderBy: [{ total_sales: 'desc' }],
});

console.log(result);
// [
//   { category_id: 1, total_sales: 45678.00 },
//   { category_id: 3, total_sales: 23456.00 },
// ]

// Multiple HAVING conditions
const result = await client.models.orders.aggregate({
  select: ['customer_id'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'order_count' },
    { type: 'SUM', field: 'total_amount', alias: 'total_amount' },
  ],
  groupBy: ['customer_id'],
  having: [
    { columnName: 'order_count', condition: { $gte: 5 } },
    { columnName: 'total_amount', condition: { $gte: 1000 } },
  ],
});

// Find VIP customers with >= 5 orders AND >= 1000 total

📋 Pagination

// Paginated category sales ranking
const result = await client.models.orders.aggregate({
  select: ['category_id'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'order_count' },
    { type: 'SUM', field: 'total_amount', alias: 'total_sales' },
  ],
  groupBy: ['category_id'],
  orderBy: [{ total_sales: 'desc' }],
  currentPage: 1,
  pageSize: 10,
});

console.log(result);
// Returns top 10 categories

🎨 Complete Examples

Example 1: Sales Dashboard

import { client } from "./api/client";

async function getSalesDashboard() {
  // 1. Overall statistics
  const overallStats = await client.models.orders.aggregate({
    aggregate: [
      { type: 'COUNT', field: '*', alias: 'total_orders' },
      { type: 'SUM', field: 'total_amount', alias: 'total_revenue', round: true, precision: 2 },
      { type: 'AVG', field: 'total_amount', alias: 'avg_order_value', round: true, precision: 2 },
    ],
    where: {
      status: { $eq: 'completed' },
    },
  });

  // 2. Statistics by category
  const categoryStats = await client.models.orders.aggregate({
    select: ['category_id', 'category_name'],
    aggregate: [
      { type: 'COUNT', field: '*', alias: 'order_count' },
      { type: 'SUM', field: 'total_amount', alias: 'category_revenue' },
    ],
    where: {
      status: { $eq: 'completed' },
    },
    groupBy: ['category_id', 'category_name'],
    orderBy: [{ category_revenue: 'desc' }],
    pageSize: 5,
  });

  return {
    overall: overallStats,
    byCategory: categoryStats,
  };
}

// Usage
const dashboard = await getSalesDashboard();
console.log('Overall:', dashboard.overall);
console.log('By Category:', dashboard.byCategory);

Example 2: React Component

import { useState, useEffect } from "react";
import { client } from "./api/client";
import { Table, Card, Statistic, Row, Col } from "antd";

function SalesReport() {
  const [loading, setLoading] = useState(false);
  const [overallStats, setOverallStats] = useState<any>({});
  const [categoryData, setCategoryData] = useState<any[]>([]);

  useEffect(() => {
    loadReport();
  }, []);

  const loadReport = async () => {
    setLoading(true);
    try {
      // Overall statistics
      const overall = await client.models.orders.aggregate({
        aggregate: [
          { type: 'COUNT', field: '*', alias: 'totalOrders' },
          { type: 'SUM', field: 'total_amount', alias: 'totalRevenue' },
          { type: 'AVG', field: 'total_amount', alias: 'avgOrderValue', round: true, precision: 2 },
        ],
        where: {
          status: { $eq: 'completed' },
        },
      });
      setOverallStats(overall);

      // Category statistics
      const byCategory = await client.models.orders.aggregate({
        select: ['category_name'],
        aggregate: [
          { type: 'COUNT', field: '*', alias: 'orderCount' },
          { type: 'SUM', field: 'total_amount', alias: 'revenue' },
        ],
        where: {
          status: { $eq: 'completed' },
        },
        groupBy: ['category_name'],
        orderBy: [{ revenue: 'desc' }],
      });
      setCategoryData(byCategory);
    } catch (error) {
      console.error("Failed to load report:", error);
    } finally {
      setLoading(false);
    }
  };

  const columns = [
    { title: "Category", dataIndex: "category_name", key: "category_name" },
    { title: "Orders", dataIndex: "orderCount", key: "orderCount" },
    {
      title: "Revenue",
      dataIndex: "revenue",
      key: "revenue",
      render: (v: number) => `$${v?.toLocaleString()}`,
    },
  ];

  return (
    <div>
      <Row gutter={16} style={{ marginBottom: 24 }}>
        <Col span={8}>
          <Card>
            <Statistic
              title="Total Orders"
              value={overallStats.totalOrders}
              loading={loading}
            />
          </Card>
        </Col>
        <Col span={8}>
          <Card>
            <Statistic
              title="Total Revenue"
              value={overallStats.totalRevenue}
              precision={2}
              prefix="$"
              loading={loading}
            />
          </Card>
        </Col>
        <Col span={8}>
          <Card>
            <Statistic
              title="Average Order"
              value={overallStats.avgOrderValue}
              precision={2}
              prefix="$"
              loading={loading}
            />
          </Card>
        </Col>
      </Row>

      <Card title="Category Sales Ranking">
        <Table
          dataSource={categoryData}
          columns={columns}
          rowKey="category_name"
          loading={loading}
          pagination={false}
        />
      </Card>
    </div>
  );
}

🆚 aggregate vs filter vs SQL

Feature	aggregate	filter	Custom SQL
Use Case	Aggregation statistics	List queries	Complex queries
Aggregate Functions	✅ SUM/COUNT/AVG	❌	✅ All
GROUP BY	✅ Native support	❌	✅
HAVING	✅ Native support	❌	✅
Multi-table JOIN	❌	✅	✅
Type Safety	✅ TypeScript	✅ TypeScript	⚠️ Manual types
Best For	Reports, stats, dashboards	Lists, search	Complex business queries

⚠️ Important Notes

1. Field Types

Ensure aggregate fields are numeric:

// ✅ Correct - numeric field
{ type: 'SUM', field: 'total_amount', alias: 'total' }

// ❌ Wrong - cannot sum string field
{ type: 'SUM', field: 'order_no', alias: 'total' }

2. COUNT field Parameter

// Count all records
{ type: 'COUNT', field: '*', alias: 'total' }

// Count non-null values
{ type: 'COUNT', field: 'customer_id', alias: 'customers_with_orders' }

// Count distinct values
{ type: 'COUNT', field: 'customer_id', alias: 'unique_customers', distinct: true }

3. HAVING vs WHERE

• WHERE: Pre-aggregation filter, reduces data for aggregation
• HAVING: Post-aggregation filter, filters aggregated results

// WHERE: Filter completed orders first, then aggregate
where: { status: { $eq: 'completed' } }

// HAVING: Aggregate all orders, only return groups with sales > 10000
having: [{ columnName: 'total_sales', condition: { $gte: 10000 } }]

4. Performance Optimization

• Use WHERE to filter data early, reducing aggregation computation
• Set reasonable pageSize to avoid returning too many grouped results
• Consider adding indexes for large tables

📚 Related Documentation

• Filter API Advanced Filtering - List data queries
• SQL API Usage Guide - Custom SQL queries
• API Usage Guide - Complete API overview
• Practical Examples - More real-world examples

---

Having issues? Check the Troubleshooting Guide or contact support.