Aggregate API

v1.2.0+

The aggregate API is the Lovrabet SDK interface for aggregated statistics. It supports aggregate functions such as SUM, COUNT, and AVG, and pairs them with GROUP BY and HAVING for complex analytical queries — the core tool for building reports and data dashboards.

💡 Prerequisite Before you start, make sure the SDK is configured. Generating the configuration automatically with the CLI is recommended.

The examples below use the alias style client.models.orders for readability; the standard style client.models.dataset_xxx works identically.

⚠️ Operation restriction The aggregate() operation is supported only in WebAPI mode (Cookie authentication). It is not yet available in OpenAPI mode.

🎯 Key features

✅ Aggregate functions — SUM, COUNT, and AVG
✅ Grouped statistics — GROUP BY aggregation
✅ Conditional filtering — WHERE to filter rows and HAVING to filter groups
✅ Result sorting — sort by aggregate results
✅ Pagination — paginate large grouped result sets
✅ Precision control — rounding and decimal places

📝 API signature

TypeScript

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

Parameters

TypeScript

interface AggregateParams {
  select?: string[];              // 选择返回的字段（通常用于 GROUP BY）
  aggregate: AggregateField[];    // 聚合配置（必填）
  where?: WhereCondition;         // 查询条件（聚合前过滤）
  groupBy?: string[];             // 分组字段
  having?: HavingCondition[];     // 分组后过滤条件
  orderBy?: SortList;             // 排序规则
  currentPage?: number;           // 当前页码
  pageSize?: number;              // 每页数量
}

Aggregate field configuration

TypeScript

interface AggregateField {
  type: 'SUM' | 'COUNT' | 'AVG' | 'MIN' | 'MAX';  // 聚合类型
  column: string;              // 字段名（COUNT 用 '*' 表示所有）
  alias?: string;              // 返回结果的字段别名
  distinct?: boolean;          // 是否去重（COUNT DISTINCT）
  round?: boolean;             // 是否四舍五入
  precision?: number;          // 小数位数（配合 round 使用）
  /** @deprecated v1.4.2 起改用 column；SDK 会把 field 自动归一化为 column */
  field?: string;
}

💡 field → column (v1.4.2): column is now the standard parameter for the aggregate field name, and the request body always sends column to the backend. The old field spelling remains fully compatible (the SDK normalizes it automatically), every field in the examples below can be replaced with column, and new code should use column.

Return values

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

📊 Aggregate functions in depth

SUM

Sums the specified field; use it on numeric fields.

TypeScript

// 计算订单总金额
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 records.

TypeScript

// 统计订单总数
const result = await client.models.orders.aggregate({
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'total_count' },
  ],
});

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

// 统计不重复的客户数
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

Averages the specified field.

TypeScript

// 计算平均订单金额
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 }

Combining aggregate functions

TypeScript

// 同时获取总数、总和、平均值
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 aggregation

Basic grouping

TypeScript

// 按状态分组统计订单
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 },
// ]

Grouping by multiple fields

TypeScript

// 按年份和月份分组统计
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 },
//   { year: 2024, month: 12, order_count: 180, total_sales: 22100.00 },
// ]

Statistics by category

TypeScript

// 按产品分类统计销量
const result = await client.models.order_items.aggregate({
  select: ['category_name'],
  aggregate: [
    { type: 'COUNT', field: '*', alias: 'item_count' },
    { type: 'SUM', field: 'quantity', alias: 'total_quantity' },
    { type: 'SUM', field: 'subtotal', alias: 'total_revenue' },
  ],
  groupBy: ['category_name'],
  orderBy: [{ total_revenue: 'desc' }],
});

console.log(result);
// [
//   { category_name: '电子产品', item_count: 500, total_quantity: 800, total_revenue: 456789.00 },
//   { category_name: '服装', item_count: 300, total_quantity: 1200, total_revenue: 123456.00 },
// ]

🔍 WHERE filtering

Filter the data before aggregating, so only matching records feed into the statistics.

TypeScript

// 统计已完成订单
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 }

// 统计指定日期范围内的订单
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 after grouping

HAVING filters aggregated results — like WHERE, but applied to the grouped output.

TypeScript

// 找出销售额超过 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 },
// ]

// 多条件 HAVING
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 } },
  ],
});

// 找出订单数 >= 5 且总金额 >= 1000 的 VIP 客户

📋 Pagination

For large grouped result sets, use pagination.

TypeScript

// 分页获取分类销售排行
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);
// 返回前 10 个分类的统计结果

🎨 Complete examples

Example 1: A sales statistics dashboard

TypeScript

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

async function getSalesDashboard() {
  // 1. 总体销售数据
  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. 按分类统计
  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,
  });

  // 3. 本月销售趋势
  const now = new Date();
  const monthStart = new Date(now.getFullYear(), now.getMonth(), 1).toISOString();

  const monthlyStats = await client.models.orders.aggregate({
    select: ['DATE(create_time) as date'],
    aggregate: [
      { type: 'COUNT', field: '*', alias: 'daily_orders' },
      { type: 'SUM', field: 'total_amount', alias: 'daily_revenue' },
    ],
    where: {
      create_time: { $gte: monthStart },
      status: { $eq: 'completed' },
    },
    groupBy: ['DATE(create_time)'],
    orderBy: [{ date: 'asc' }],
  });

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

// 使用
const dashboard = await getSalesDashboard();
console.log('总体数据:', dashboard.overall);
console.log('分类排行:', dashboard.byCategory);
console.log('本月趋势:', dashboard.monthlyTrend);

Example 2: A customer analytics report

TypeScript

// 客户消费分析
async function analyzeCustomers() {
  // 客户消费排名 TOP 10
  const topCustomers = await client.models.orders.aggregate({
    select: ['customer_id', 'customer_name'],
    aggregate: [
      { type: 'COUNT', field: '*', alias: 'order_count' },
      { type: 'SUM', field: 'total_amount', alias: 'total_spent' },
      { type: 'AVG', field: 'total_amount', alias: 'avg_order', round: true, precision: 2 },
    ],
    groupBy: ['customer_id', 'customer_name'],
    orderBy: [{ total_spent: 'desc' }],
    pageSize: 10,
  });

  // 客户分层统计
  const customerSegments = await client.models.customers.aggregate({
    select: ['level'],
    aggregate: [
      { type: 'COUNT', field: '*', alias: 'customer_count' },
      { type: 'SUM', field: 'total_purchases', alias: 'total_purchases' },
    ],
    groupBy: ['level'],
    orderBy: [{ customer_count: 'desc' }],
  });

  // 高价值客户（消费超过 5000）
  const vipCustomers = await client.models.orders.aggregate({
    select: ['customer_id'],
    aggregate: [
      { type: 'SUM', field: 'total_amount', alias: 'lifetime_value' },
    ],
    groupBy: ['customer_id'],
    having: [
      { columnName: 'lifetime_value', condition: { $gte: 5000 } },
    ],
    orderBy: [{ lifetime_value: 'desc' }],
  });

  return {
    topCustomers,
    segments: customerSegments,
    vipCount: vipCustomers.length,
  };
}

Example 3: React component integration

TypeScript

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 {
      // 总体统计
      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);

      // 分类统计
      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("加载报表失败:", error);
    } finally {
      setLoading(false);
    }
  };

  const columns = [
    { title: "分类", dataIndex: "category_name", key: "category_name" },
    { title: "订单数", dataIndex: "orderCount", key: "orderCount" },
    {
      title: "销售额",
      dataIndex: "revenue",
      key: "revenue",
      render: (v: number) => `¥${v?.toLocaleString()}`,
    },
  ];

  return (
    <div>
      <Row gutter={16} style={{ marginBottom: 24 }}>
        <Col span={8}>
          <Card>
            <Statistic
              title="总订单数"
              value={overallStats.totalOrders}
              loading={loading}
            />
          </Card>
        </Col>
        <Col span={8}>
          <Card>
            <Statistic
              title="总销售额"
              value={overallStats.totalRevenue}
              precision={2}
              prefix="¥"
              loading={loading}
            />
          </Card>
        </Col>
        <Col span={8}>
          <Card>
            <Statistic
              title="平均订单金额"
              value={overallStats.avgOrderValue}
              precision={2}
              prefix="¥"
              loading={loading}
            />
          </Card>
        </Col>
      </Row>

      <Card title="分类销售排行">
        <Table
          dataSource={categoryData}
          columns={columns}
          rowKey="category_name"
          loading={loading}
          pagination={false}
        />
      </Card>
    </div>
  );
}

🆚 aggregate vs filter vs custom SQL

Feature	aggregate	filter	custom SQL
Purpose	Aggregated 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	⚠️ Types written by hand
Best suited for	Reports, statistics, dashboards	Lists, search	Complex business queries

⚠️ Notes

1. Field types

Make sure the aggregated field is numeric:

TypeScript

// ✅ 正确 - 数值字段
{ type: 'SUM', field: 'total_amount', alias: 'total' }

// ❌ 错误 - 字符串字段无法求和
{ type: 'SUM', field: 'order_no', alias: 'total' }

2. The `field` parameter of COUNT

TypeScript

// 统计所有记录数
{ type: 'COUNT', field: '*', alias: 'total' }

// 统计某字段非空记录数
{ type: 'COUNT', field: 'customer_id', alias: 'customers_with_orders' }

// 统计不重复值
{ type: 'COUNT', field: 'customer_id', alias: 'unique_customers', distinct: true }

3. GROUP BY field order

With multiple GROUP BY fields, results are grouped in field order:

TypeScript

// 先按年份，再按月份
groupBy: ['year', 'month']

// 结果结构：2025 → 1月, 2025 → 2月, 2024 → 12月...

4. HAVING vs WHERE

WHERE filters before aggregation, reducing the amount of data that gets aggregated
HAVING filters after aggregation, selecting from the aggregated results

TypeScript

// WHERE：先筛选已完成订单，再统计
where: { status: { $eq: 'completed' } }

// HAVING：统计所有订单，只返回销售额 > 10000 的分组
having: [{ columnName: 'total_sales', condition: { $gte: 10000 } }]

5. Performance

Use WHERE to filter early and shrink the aggregation workload
Set a sensible pageSize so grouped results don't flood back
Add indexes on large tables

Filter API — advanced filtered queries for list data
SQL API guide — custom SQL queries
API usage guide — the complete API overview
Real-world examples — more hands-on examples

Questions? See the troubleshooting guide or contact technical support.

Aggregate API ​

🎯 Key features ​

📝 API signature ​

Parameters ​

Aggregate field configuration ​

Return values ​

📊 Aggregate functions in depth ​

SUM ​

COUNT ​

AVG ​

Combining aggregate functions ​

🔀 GROUP BY aggregation ​

Basic grouping ​

Grouping by multiple fields ​

Statistics by category ​

🔍 WHERE filtering ​

🎯 HAVING: filtering after grouping ​

📋 Pagination ​

🎨 Complete examples ​

Example 1: A sales statistics dashboard ​

Example 2: A customer analytics report ​

Example 3: React component integration ​

🆚 aggregate vs filter vs custom SQL ​

⚠️ Notes ​

1. Field types ​

2. The field parameter of COUNT ​

3. GROUP BY field order ​

4. HAVING vs WHERE ​

5. Performance ​

📚 Related documentation ​

Aggregate API

🎯 Key features

📝 API signature

Parameters

Aggregate field configuration

Return values

📊 Aggregate functions in depth

SUM

COUNT

AVG

Combining aggregate functions

🔀 GROUP BY aggregation

Basic grouping

Grouping by multiple fields

Statistics by category

🔍 WHERE filtering

🎯 HAVING: filtering after grouping

📋 Pagination

🎨 Complete examples

Example 1: A sales statistics dashboard

Example 2: A customer analytics report

Example 3: React component integration

🆚 aggregate vs filter vs custom SQL

⚠️ Notes

1. Field types

2. The `field` parameter of COUNT

3. GROUP BY field order

4. HAVING vs WHERE

5. Performance

📚 Related documentation