Aggregates

Soy provides aggregate functions for counting and computing statistics. All aggregates return float64 to handle various numeric types uniformly.

See API Reference for complete method signatures.

COUNT

Count All Rows

count, err := users.Count().Exec(ctx, nil)
// count is float64
fmt.Printf("Total users: %.0f\n", count)

Count with WHERE

count, err := users.Count().
    Where("status", "=", "status").
    Exec(ctx, map[string]any{"status": "active"})

SUM

Sum a numeric field:

total, err := orders.Sum("amount").
    Where("status", "=", "status").
    Exec(ctx, map[string]any{"status": "completed"})

fmt.Printf("Total revenue: $%.2f\n", total)

AVG

Calculate average:

avgAge, err := users.Avg("age").
    Where("status", "=", "status").
    Exec(ctx, map[string]any{"status": "active"})

MIN and MAX

// Minimum
minPrice, err := products.Min("price").Exec(ctx, nil)

// Maximum
maxScore, err := scores.Max("value").
    Where("game_id", "=", "game").
    Exec(ctx, map[string]any{"game": 123})

NULL Handling

Aggregate functions return 0.0 when the result is NULL (e.g., no matching rows):

sum, err := orders.Sum("amount").
    Where("user_id", "=", "user").
    Exec(ctx, map[string]any{"user": 999})  // User with no orders

// sum == 0.0, err == nil

GROUP BY

Use Query with aggregate expressions for grouped results:

type StatusCount struct {
    Status string `db:"status"`
    Count  int    `db:"count"`
}

// Note: This requires a custom query or raw SQL
// Soy aggregates return single values

For grouped aggregates, use the Query builder with custom fields:

results, err := users.Query().
    Fields("status", "COUNT(*) as count").
    GroupBy("status").
    Exec(ctx, nil)

HAVING

Filter aggregate results:

results, err := orders.Query().
    Fields("customer_id", "SUM(amount) as total").
    GroupBy("customer_id").
    Having("total", ">=", "min_total").
    Exec(ctx, map[string]any{"min_total": 1000})

Aggregate HAVING

Use aggregate functions in HAVING:

results, err := orders.Query().
    Fields("customer_id").
    GroupBy("customer_id").
    HavingAgg("COUNT", "*", ">=", "min_orders").
    Exec(ctx, map[string]any{"min_orders": 5})

SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= :min_orders

Combined WHERE and HAVING

results, err := orders.Query().
    Fields("customer_id", "SUM(amount) as total").
    Where("created_at", ">=", "start_date").
    GroupBy("customer_id").
    HavingAgg("SUM", "amount", ">=", "threshold").
    OrderBy("total", "desc").
    Limit(10).
    Exec(ctx, map[string]any{
        "start_date": time.Now().AddDate(0, -1, 0),
        "threshold":  500,
    })

Transactions

Aggregates support transaction execution:

tx, err := db.BeginTxx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

count, err := orders.Count().
    Where("user_id", "=", "user").
    ExecTx(ctx, tx, params)
if err != nil {
    return err
}

// Use count in subsequent operations...

Inspecting SQL

result, err := users.Count().
    Where("status", "=", "status").
    Render()

fmt.Println(result.SQL)
// SELECT COUNT(*) FROM users WHERE status = :status

Complete Example

// Dashboard statistics
func GetDashboardStats(ctx context.Context, userID int) (*Stats, error) {
    params := map[string]any{"user_id": userID}

    totalOrders, err := orders.Count().
        Where("user_id", "=", "user_id").
        Exec(ctx, params)
    if err != nil {
        return nil, err
    }

    totalSpent, err := orders.Sum("amount").
        Where("user_id", "=", "user_id").
        Where("status", "=", "status").
        Exec(ctx, map[string]any{
            "user_id": userID,
            "status":  "completed",
        })
    if err != nil {
        return nil, err
    }

    avgOrderValue, err := orders.Avg("amount").
        Where("user_id", "=", "user_id").
        Exec(ctx, params)
    if err != nil {
        return nil, err
    }

    return &Stats{
        TotalOrders:   int(totalOrders),
        TotalSpent:    totalSpent,
        AvgOrderValue: avgOrderValue,
    }, nil
}