$group

// 1) Basic — total revenue per customer
db.orders.aggregate([
    { $match: { status: 'paid' } },
    { $group: {
        _id: '$customerId',
        revenue: { $sum: '$totalCents' },
        count:   { $sum: 1 },
    } },
]);

// 2) Common accumulators
// $sum     — sum of an expression (use 1 for count)
// $avg     — average
// $min     — minimum
// $max     — maximum
// $first   — first value (in input order or sort order)
// $last    — last value
// $push    — array of all values (warning: doc size limit 16 MB)
// $addToSet — array of DISTINCT values
// $mergeObjects — combine documents
// $stdDevPop / $stdDevSamp — standard deviation
// $topN / $bottomN (5.2+) — top/bottom N by sort
// $percentile / $median (7.0+)

// 3) Multi-field _id — group by composite key
db.orders.aggregate([
    { $group: {
        _id: { customer: '$customerId', month: { $dateTrunc: { date: '$createdAt', unit: 'month' } } },
        revenue: { $sum: '$totalCents' },
    } },
    { $sort: { '_id.month': 1 } },
]);

// 4) Group then $project to flatten the _id
db.orders.aggregate([
    { $group: { _id: '$customerId', revenue: { $sum: '$totalCents' } } },
    { $project: { _id: 0, customerId: '$_id', revenue: 1 } },
]);

// 5) Group with all rows in an array (careful — 16 MB doc limit)
db.orders.aggregate([
    { $group: {
        _id: '$customerId',
        orders: { $push: { id: '$_id', amount: '$totalCents', at: '$createdAt' } },
    } },
]);

// Better for large groups — limit array length
db.orders.aggregate([
    { $sort: { createdAt: -1 } },
    { $group: {
        _id: '$customerId',
        recentOrders: { $push: { id: '$_id', amount: '$totalCents' } },
    } },
    { $project: { recentOrders: { $slice: ['$recentOrders', 5] } } },
]);

// Or use $topN (5.2+) — efficient + correct
db.orders.aggregate([
    { $group: {
        _id: '$customerId',
        top5: { $topN: { output: { id: '$_id', amount: '$totalCents' }, sortBy: { totalCents: -1 }, n: 5 } },
    } },
]);

// 6) Conditional sums — like SUM(CASE WHEN ...) in SQL
db.orders.aggregate([
    { $group: {
        _id: '$customerId',
        paid_count:    { $sum: { $cond: [{ $eq: ['$status', 'paid'] }, 1, 0] } },
        refund_count:  { $sum: { $cond: [{ $eq: ['$status', 'refunded'] }, 1, 0] } },
        paid_revenue:  { $sum: { $cond: [{ $eq: ['$status', 'paid'] }, '$totalCents', 0] } },
    } },
]);

// 7) Group + secondary aggregations
db.orders.aggregate([
    { $group: { _id: '$customerId', revenue: { $sum: '$totalCents' }, count: { $sum: 1 } } },
    { $group: {
        _id: null,
        customerCount: { $sum: 1 },
        totalRevenue:  { $sum: '$revenue' },
        avgRevenue:    { $avg: '$revenue' },
        avgOrderCount: { $avg: '$count' },
    } },
]);

// 8) Group by date bucket
db.orders.aggregate([
    { $match: { createdAt: { $gte: ISODate('2024-01-01') } } },
    { $group: {
        _id: { day: { $dateTrunc: { date: '$createdAt', unit: 'day', timezone: 'Australia/Sydney' } } },
        revenue: { $sum: '$totalCents' },
        orders:  { $sum: 1 },
    } },
    { $sort: { '_id.day': 1 } },
]);

// 9) Distinct count
db.orders.aggregate([
    { $group: { _id: '$customerId' } },
    { $count: 'distinct_customers' },
]);

// Or:
db.orders.distinct('customerId').length;     // simpler when no other ops needed

// 10) Group + lookup (join) — bring back customer details
db.orders.aggregate([
    { $group: { _id: '$customerId', revenue: { $sum: '$totalCents' } } },
    { $lookup: { from: 'customers', localField: '_id', foreignField: '_id', as: 'customer' } },
    { $unwind: '$customer' },
    { $project: { _id: 0, customer: '$customer.name', revenue: 1 } },
]);

// 11) $facet — multiple groupings in ONE pass
db.orders.aggregate([
    { $facet: {
        by_customer: [{ $group: { _id: '$customerId', revenue: { $sum: '$totalCents' } } }],
        by_status:   [{ $group: { _id: '$status', count: { $sum: 1 } } }],
        global:      [{ $group: { _id: null, total: { $sum: '$totalCents' } } }],
    } },
]);

// 12) Performance
// • Put $match FIRST — index-backed filter trims input
// • Indexes on the _id expression help when grouping by indexed field with no $match
// • $group is RAM-bound — use allowDiskUse: true for huge groups
// • Use the cheapest accumulator that meets your need: $count > $sum > $push
// • Avoid $push of full documents; only push fields you need

db.orders.aggregate(pipeline, { allowDiskUse: true });

// 13) Common bugs
// • _id: '$field' returns ONE row per distinct value — that's the whole point; surprising to SQL users
// • Forgetting to enclose $cond branches → syntax error
// • Summing strings — coerces to NaN; ensure numeric values; check with $type
// • Using $push and hitting 16 MB doc limit → switch to $topN or paginate
// • Group BEFORE $lookup → joined data missing; $lookup needs to come first (or join with the grouped key)
// • Date grouping in wrong timezone → daily buckets off by 12 hours; specify timezone
// • Missing $match before group on huge collections → slow + RAM bloat

// Sum totals per customer
{ $group: { _id: '$customer_id', total: { $sum: '$total' } } }