Skip to main content

Aggregations

Learn how to perform calculations on your data using Ductape's aggregation API. This guide covers counting, summing, averaging, grouping, and multi-aggregation operations.

Quick Example​

const stats = await ductape.database.aggregate({
  table: 'orders',
  operations: {
    total_orders: { $count: '*' },
    total_revenue: { $sum: 'total' },
    avg_order_value: { $avg: 'total' },
    min_order: { $min: 'total' },
    max_order: { $max: 'total' },
  },
  where: { status: 'completed' },
});

console.log('Total Orders:', stats.total_orders);
console.log('Total Revenue:', stats.total_revenue);
console.log('Average Order:', stats.avg_order_value);

Aggregation Syntax​

Ductape uses a clean, object-based syntax for aggregations with lowercase operators following the Mongoose/MongoDB convention:

{
  alias_name: { $function: 'column_name' }
}

Supported Functions​

FunctionDescriptionExample
$countCount records{ total: { $count: '*' } }
$sumSum of values{ revenue: { $sum: 'amount' } }
$avgAverage value{ avg_price: { $avg: 'price' } }
$minMinimum value{ lowest: { $min: 'price' } }
$maxMaximum value{ highest: { $max: 'price' } }
$stringAggConcatenate strings (PostgreSQL){ names: { $stringAgg: { column: 'name', separator: ', ' } } }
$groupConcatConcatenate strings (MySQL){ names: { $groupConcat: { column: 'name', separator: ', ' } } }
$arrayAggCollect into array{ ids: { $arrayAgg: 'id' } }
Backwards Compatibility

Uppercase operators (e.g., $COUNT, $SUM) are still supported for backwards compatibility, but lowercase is recommended.

Count​

Count records with optional filtering:

Simple Count​

const total = await ductape.database.count({
  table: 'users',
});

console.log('Total users:', total);

Count with Filter​

const activeUsers = await ductape.database.count({
  table: 'users',
  where: { status: 'active' },
});

Count Distinct Values​

const uniqueCategories = await ductape.database.count({
  table: 'products',
  column: 'category',
  distinct: true,
});

Count Specific Column​

Count non-null values in a column:

const usersWithEmail = await ductape.database.count({
  table: 'users',
  column: 'email', // Only counts rows where email is not null
});

Sum​

Calculate the sum of numeric values:

const totalRevenue = await ductape.database.sum({
  table: 'orders',
  column: 'total',
  where: { status: 'completed' },
});

console.log('Total revenue:', totalRevenue);

Sum with Complex Filters​

const monthlyRevenue = await ductape.database.sum({
  table: 'orders',
  column: 'total',
  where: {
    $and: [
      { status: 'completed' },
      { created_at: { $gte: new Date('2024-01-01') } },
      { created_at: { $lt: new Date('2024-02-01') } },
    ],
  },
});

Average​

Calculate the average of numeric values:

const avgOrderValue = await ductape.database.avg({
  table: 'orders',
  column: 'total',
  where: { status: 'completed' },
});

console.log('Average order value:', avgOrderValue);

Min / Max​

Find minimum or maximum values:

// Minimum value
const lowestPrice = await ductape.database.min({
  table: 'products',
  column: 'price',
  where: { status: 'active' },
});

// Maximum value
const highestPrice = await ductape.database.max({
  table: 'products',
  column: 'price',
  where: { status: 'active' },
});

console.log('Price range:', lowestPrice, '-', highestPrice);

Multi-Aggregation​

Perform multiple aggregations in a single query:

const stats = await ductape.database.aggregate({
  table: 'orders',
  operations: {
    total_orders: { $count: '*' },
    total_revenue: { $sum: 'total' },
    avg_order_value: { $avg: 'total' },
    min_order: { $min: 'total' },
    max_order: { $max: 'total' },
  },
  where: { status: 'completed' },
});

console.log('Statistics:', stats);
// {
//   total_orders: 1234,
//   total_revenue: 98765.43,
//   avg_order_value: 80.04,
//   min_order: 10.00,
//   max_order: 500.00
// }

Group By​

Group records and aggregate within each group:

Basic Group By​

const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['status'],
  aggregate: {
    count: { $count: '*' },
    total: { $sum: 'total' },
  },
});

result.forEach((group) => {
  console.log(`${group.status}: ${group.count} orders, $${group.total}`);
});

// pending: 50 orders, $2500
// completed: 200 orders, $15000
// cancelled: 10 orders, $500

Multiple Group By Columns​

const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['status', 'payment_method'],
  aggregate: {
    total_orders: { $count: '*' },
    total_amount: { $sum: 'total' },
    avg_amount: { $avg: 'total' },
  },
});

result.forEach((group) => {
  console.log('Status:', group.status);
  console.log('Payment Method:', group.payment_method);
  console.log('Total Orders:', group.total_orders);
  console.log('Total Amount:', group.total_amount);
  console.log('---');
});

Group By with Filters​

const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['category'],
  aggregate: {
    count: { $count: '*' },
    revenue: { $sum: 'total' },
  },
  where: {
    created_at: { $gte: new Date('2024-01-01') },
    status: 'completed',
  },
});

Group By with HAVING​

Filter groups after aggregation:

const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['customer_id'],
  aggregate: {
    order_count: { $count: '*' },
    total_spent: { $sum: 'total' },
  },
  having: {
    order_count: { $gt: 10 },      // Customers with more than 10 orders
    total_spent: { $gte: 1000 },   // Who spent at least $1000
  },
});

Group By with Ordering​

const result = await ductape.database.groupBy({
  table: 'products',
  groupBy: ['category'],
  aggregate: {
    product_count: { $count: '*' },
    avg_price: { $avg: 'price' },
  },
  orderBy: { column: 'product_count', order: 'DESC' },
  limit: 10, // Top 10 categories
});

Database-Specific Functions​

PostgreSQL​

// String aggregation
const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['customer_id'],
  aggregate: {
    order_ids: { $stringAgg: { column: 'id', separator: ', ' } },    // "1, 2, 3, 4"
    products: { $arrayAgg: 'product_id' }, // [1, 2, 3, 4]
  },
});

MySQL​

// Group concatenation
const result = await ductape.database.groupBy({
  table: 'orders',
  groupBy: ['customer_id'],
  aggregate: {
    product_names: { $groupConcat: { column: 'product_name', separator: ',' } }, // "Product A,Product B,Product C"
  },
});

MongoDB​

// Push to array (equivalent to $arrayAgg)
const result = await ductape.database.groupBy({
  table: 'orders', // Collection name
  groupBy: ['customer_id'],
  aggregate: {
    order_ids: { $arrayAgg: '_id' },
    total_spent: { $sum: 'total' },
  },
});

Use Cases​

Dashboard Statistics​

async function getDashboardStats() {
  const [orderStats, userStats, productStats] = await Promise.all([
    ductape.database.aggregate({
      table: 'orders',
      operations: {
        total_orders: { $count: '*' },
        total_revenue: { $sum: 'total' },
        avg_order: { $avg: 'total' },
      },
      where: {
        created_at: { $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
      },
    }),

    ductape.database.count({
      table: 'users',
      where: {
        created_at: { $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
      },
    }),

    ductape.database.count({
      table: 'products',
      where: { status: 'active' },
    }),
  ]);

  return {
    orders: orderStats,
    newUsers: userStats,
    activeProducts: productStats,
  };
}

Sales by Category​

async function getSalesByCategory(startDate: Date, endDate: Date) {
  return ductape.database.groupBy({
    table: 'order_items',
    groupBy: ['category'],
    aggregate: {
      units_sold: { $sum: 'quantity' },
      revenue: { $sum: 'total' },
      avg_price: { $avg: 'unit_price' },
    },
    where: {
      created_at: { $between: [startDate, endDate] },
    },
    orderBy: { column: 'revenue', order: 'DESC' },
  });
}

Top Customers​

async function getTopCustomers(limit: number = 10) {
  return ductape.database.groupBy({
    table: 'orders',
    groupBy: ['customer_id'],
    aggregate: {
      order_count: { $count: '*' },
      total_spent: { $sum: 'total' },
      avg_order: { $avg: 'total' },
      first_order: { $min: 'created_at' },
      last_order: { $max: 'created_at' },
    },
    where: { status: 'completed' },
    orderBy: { column: 'total_spent', order: 'DESC' },
    limit,
  });
}

Daily Revenue Report​

async function getDailyRevenue(days: number = 30) {
  // Note: Date grouping requires raw query for some databases
  return ductape.database.raw({
    query: `
      SELECT
        DATE(created_at) as date,
        COUNT(*) as order_count,
        SUM(total) as revenue,
        AVG(total) as avg_order
      FROM orders
      WHERE created_at >= $1 AND status = 'completed'
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `,
    params: [new Date(Date.now() - days * 24 * 60 * 60 * 1000)],
  });
}

Aggregation Options Reference​

Count Options​

OptionTypeDescription
tablestringTable name
columnstringColumn to count (default: *)
whereobjectFilter conditions
distinctbooleanCount distinct values

Sum/Avg/Min/Max Options​

OptionTypeDescription
tablestringTable name
columnstringColumn to aggregate
whereobjectFilter conditions

Aggregate Options​

OptionTypeDescription
tablestringTable name
operationsobjectAggregation operations
whereobjectFilter conditions
groupBystring[]Group by columns
havingobjectFilter aggregated groups

Group By Options​

OptionTypeDescription
tablestringTable name
groupBystring[]Columns to group by
aggregateobjectAggregation operations
whereobjectFilter conditions (before grouping)
havingobjectFilter conditions (after grouping)
orderByobjectSort configuration
limitnumberMaximum groups to return
offsetnumberGroups to skip

Performance Considerations​

DynamoDB Warning​

DynamoDB does not support native aggregations. Ductape emulates aggregations by scanning the entire table into memory. For large tables, this can be:

  • Slow - Full table scan required
  • Expensive - Consumes read capacity
  • Memory intensive - All data loaded into memory

For DynamoDB, consider:

  • Maintaining pre-computed aggregates
  • Using AWS Athena for analytics
  • Limiting aggregations to filtered subsets

Indexing​

Ensure columns used in WHERE and GROUP BY are properly indexed:

// Create index for common aggregation patterns
await ductape.database.createIndex({
  table: 'orders',
  index: {
    name: 'idx_orders_status_created',
    table: 'orders',
    columns: [
      { name: 'status' },
      { name: 'created_at', order: 'DESC' },
    ],
  },
});

Next Steps​

See Also​