catalog.fn.aggregate()

Compute numeric statistics (sum, avg) and count, min/max, distinct values and groups on any attribute, for analytics dashboards, KPI displays, and data summaries. min/max compare strings lexically and skip dates.

Syntax

list.catalog.fn.aggregate(options)

Parameters

attributestringRequired: The attribute to aggregate (supports deep queries like "product.price")
typesAggregationType[]Required: Array of aggregation types: "sum", "avg", "count", "min", "max", "distinct", "group"
excludeUndefinedsbooleanOptional: Leave the entry for undefined values out of distinct (default: false). sum, avg, min, max and count always skip missing values
distinctOptions.limitnumberOptional: Return at most this many distinct values: the first ones in index order, not the most frequent
distinctOptions.skipKeysbooleanOptional: Skip returning item keys for distinct values (default: false)
groupOptions.groupsAggregationGroupOption[]Optional: Groups for the "group" type: ranges ({ from: 0, to: 50 }, both bounds exclusive) or value lists ({ value: ["a", "b"] })
groupOptions.calculateNotFoundbooleanOptional: Append an entry (option.value: ["$dcupl_not_found"]) counting the items that match no group

Return type

Returns an Aggregation object with the requested metrics:

// Can be imported from @dcupl/common
type Aggregation = {
  attribute: string;
  types: AggregationType[];
  sum?: number;        // Total of all numeric values
  avg?: number;        // Average (mean) of numeric values
  count?: number;      // Number of non-null values (array values counted individually)
  min?: number;        // Minimum value (numbers, strings and booleans; Date values are skipped)
  max?: number;        // Maximum value (same types as min)
  distinct?: Array<{   // Unique values with counts
    value: string;     // The raw value (a number for numeric attributes)
    count: number;
    keys?: string[];   // Omitted with distinctOptions.skipKeys
  }>;
  group?: Array<{      // One entry per groupOptions.groups entry
    option: { from?: any; to?: any; value?: any[] }; // The group definition
    count: number;
    keys: string[];
  }>;
}

Usage notes

Aggregations respect current query filters - metrics update as filters change
Use distinctOptions.limit for large datasets to limit returned distinct values
Supports deep property queries (e.g., "product.brand.rating")
Performance: Request only the aggregation types you need - each type adds computation cost
Performance: Use distinctOptions.skipKeys: true when you only need values, not item references
Performance: For attributes with many distinct values, set distinctOptions.limit to keep the result small
Groups may overlap: an item is counted in every group whose range or value list it matches
Troubleshooting: If sum/avg are undefined, no numeric value was found - verify the attribute is a numeric type. min/max are undefined when no number, string or boolean was found (Date values are skipped); count, distinct and group are always set (an empty list gives count: 0 and distinct: [])
Missing values never skew sum/avg/min/max/count; excludeUndefineds only removes the undefined entry from distinct
excludeZeros, excludeUnresolved and groupOptions.limit are not aggregate() options: SDK 2.0 removed them from AggregationOptions (they never had an effect), so passing one is a type error. facets() keeps excludeZeros and excludeUnresolved

Related methods

catalog.fn.facets() — Get unique values with counts for filter UIs
catalog.fn.groupBy() — Group items with aggregations per group
catalog.fn.pivot() — Create multi-dimensional pivot tables

Related guides/docs

Functions API reference: global dcupl.fn.* form and errors

Examples

Dashboard KPIs

Calculate key metrics for a dashboard including revenue, order count, average order value, and unique customers.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

// Filter to last 30 days
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
orderList.catalog.query.addCondition({
  operator: 'gte',
  attribute: 'orderDate',
  value: thirtyDaysAgo,
});

// Revenue metrics
const revenue = orderList.catalog.fn.aggregate({
  attribute: 'totalAmount',
  types: ['sum', 'avg', 'count'],
});

// Customer metrics
const customers = orderList.catalog.fn.aggregate({
  attribute: 'customer',
  types: ['distinct'],
});

console.log('Total Revenue:', revenue.sum);
console.log('Orders:', revenue.count);
console.log('Avg Order Value:', revenue.avg);
// count, distinct and group are always set; sum and avg are undefined
// when no numeric value matched (e.g. no orders in the window)
const uniqueCustomers = customers.distinct!.length;
console.log('Unique Customers:', uniqueCustomers);
console.log('Revenue per Customer:', uniqueCustomers ? (revenue.sum ?? 0) / uniqueCustomers : 0);

Customer lifetime value

Sum the orders per customer in one call with groupBy() and its aggregates, instead of one filtered aggregate() per customer.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

// One group per customer, with the sum of its orders
const byCustomer = orderList.catalog.fn.groupBy({
  attribute: 'customer',
  aggregates: [{ attribute: 'totalAmount', types: ['sum'] }],
});

const customerLTV = byCustomer.items.map((group) => ({
  customer: group.key,
  ltv: group._aggregates[0]?.sum ?? 0,
  orders: group._meta.size,
}));

// Sort by LTV descending
customerLTV.sort((a, b) => b.ltv - a.ltv);

Discount analysis

Analyze discounts given across orders, calculate total discount amount and effective discount rate.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

// Filter to orders with discounts
orderList.catalog.query.addCondition({
  operator: 'gt',
  attribute: 'discountAmount',
  value: 0,
});

const discountAgg = orderList.catalog.fn.aggregate({
  attribute: 'discountAmount',
  types: ['sum', 'avg', 'count'],
});

const revenueAgg = orderList.catalog.fn.aggregate({
  attribute: 'totalAmount',
  types: ['sum'],
});

console.log('Orders with Discount:', discountAgg.count);
console.log('Total Discounts Given:', discountAgg.sum);
console.log('Avg Discount per Order:', discountAgg.avg);
console.log('Revenue (after discount):', revenueAgg.sum);

// sum is undefined when no order matched, so default to 0
const discounts = discountAgg.sum ?? 0;
const gross = (revenueAgg.sum ?? 0) + discounts;
console.log('Discount Rate:', gross ? (discounts / gross) * 100 : 0, '%');

Deep property aggregation

Aggregate values from referenced models using dot notation for deep property access.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

// Aggregate on a deep property (product's brand rating)
const brandRatings = orderList.catalog.fn.aggregate({
  attribute: 'product.brand.rating',
  types: ['avg', 'min', 'max'],
});

console.log('Brand Ratings (from orders):');
console.log('  Average:', brandRatings.avg);
console.log('  Lowest:', brandRatings.min);
console.log('  Highest:', brandRatings.max);

// Distinct brands ordered (limit keeps the first 10 in index order, not the 10 most frequent)
const distinctBrands = orderList.catalog.fn.aggregate({
  attribute: 'product.brand.name',
  types: ['distinct'],
  distinctOptions: { limit: 10 },
});

console.log('Brands Ordered (first 10):', distinctBrands.distinct!.map(b => b.value));

Price bands with group

Count items per custom range. Both range bounds are exclusive: a price of exactly 100 is in neither band below.

const productList = dcupl.lists.create({ modelKey: 'Product' });

const bands = productList.catalog.fn.aggregate({
  attribute: 'price',
  types: ['group'],
  groupOptions: {
    groups: [
      { from: 0, to: 100 },   // 0 < price < 100
      { from: 100, to: 500 }, // 100 < price < 500
    ],
    calculateNotFound: true, // extra entry for items in no band
  },
});

bands.group?.forEach((g) => {
  const label = g.option.value ? 'other' : g.option.from + ' < price < ' + g.option.to;
  console.log(label + ': ' + g.count + ' products');
});

// Get all numeric stats for price
const stats = list.catalog.fn.aggregate({
  attribute: 'price',
  types: ['sum', 'avg', 'min', 'max', 'count']
});

console.log('Sum:', stats.sum);
console.log('Average:', stats.avg);
console.log('Min:', stats.min);
console.log('Max:', stats.max);
console.log('Count:', stats.count);

{}