Back to DocumentationMQL Documentation

$sum

The `$sum` operator calculates the sum of numeric values of a field or expression that match a filtering criteria.

Syntax

{
  $sum: <field or expression>
}

Parameters

field or expressionobjectrequired

The field or expression to calculate the sum of. This can be a field, a numeric value, or an expression.

Examples

Sample Data

{
  "_id": "0fcc0bf0-ed18-4ab8-b558-9848e18058f4",
  "name": "First Up Consultants | Beverage Shop",
  "sales": {
    "totalSales": 75670,
    "salesByCategory": [
      { "categoryName": "Wine Accessories", "totalSales": 34440 }
    ]
  },
  "promotionEvents": [
    {
      "discounts": [
        { "categoryName": "Whiskey", "discountPercentage": 7 }
      ]
    }
  ]
}

Calculate total discount percentage per category

Compute total discount percentage per category across all promotion events in 2023.

This query unwinds promotion events, filters by year, and sums discount percentages by category.

Query:

db.stores.aggregate([
  { $unwind: "$promotionEvents" },
  { $unwind: "$promotionEvents.discounts" },
  {
    $match: {
      "promotionEvents.promotionalDates.startDate.Year": 2023
    }
  },
  {
    $group: {
      _id: "$promotionEvents.discounts.categoryName",
      totalDiscountIn2023: {
        $sum: "$promotionEvents.discounts.discountPercentage"
      }
    }
  }
])

Output:

[
  {
    "categoryName": "Glass Frames",
    "totalDiscountIn2023": 25
  },
  {
    "categoryName": "Picture Hanging Supplies",
    "totalDiscountIn2023": 14
  }
]

Using $sum in $bucket

Get the total sales within defined sales boundaries using $bucket.

This query creates sales buckets and sums total sales within each bucket.

Query:

db.stores.aggregate([
  {
    $bucket: {
      groupBy: "$sales.totalSales",
      boundaries: [0, 10000, 20000, 50000, 100000],
      default: "Other",
      output: {
        totalSalesSum: {
          $sum: "$sales.totalSales"
        },
        count: { $sum: 1 }
      }
    }
  }
])

Output:

[
  {
    "_id": "Other",
    "totalSalesSum": 454981695,
    "count": 3001
  },
  {
    "_id": 0,
    "totalSalesSum": 20033725,
    "count": 3903
  }
]

Related