Grouping, counting and summing

Choose a group key first, then select an accumulator. Counting records with $sum: 1 is different from adding a quantity field or computing price multiplied by quantity.

The aggregation framework in MongoDB is a powerful tool that lets you transform and analyze data. One of the most common use cases involves grouping documents, counting them, and summing values across groups.

Before working through the examples, make sure you can connect with mongosh and select your database. Use a practice database for writes and deletions.

Imagine you have a collection of sales data and you want to find:

• How many orders each customer placed
• Total sales per product
• Average quantity ordered by region

These kinds of operations require grouping and aggregating the data — something SQL does with GROUP BY. In MongoDB, we use $group and aggregation pipeline stages.

Sample Data Setup

Let’s use a sales collection with the following documents:

    db.sales.insertMany([
      { customer: "Alice", product: "Laptop", quantity: 1, amount: 70000 },
      { customer: "Bob", product: "Laptop", quantity: 1, amount: 70000 },
      { customer: "Alice", product: "Mouse", quantity: 2, amount: 1200 },
      { customer: "Charlie", product: "Laptop", quantity: 1, amount: 70000 },
      { customer: "Alice", product: "Keyboard", quantity: 1, amount: 2500 },
      { customer: "Bob", product: "Mouse", quantity: 1, amount: 600 }
    ]);

We want to count how many orders each customer placed.

    db.sales.aggregate([
      {
        $group: {
          _id: "$customer",
          totalOrders: { $sum: 1 }
        }
      }
    ]);

    { "_id": "Alice", "totalOrders": 3 }
    { "_id": "Bob", "totalOrders": 2 }
    { "_id": "Charlie", "totalOrders": 1 }

Explanation: We use $group to group by customer and count the documents in each group by adding $sum: 1.

Use Case 2: Total Sales per Product

Now let’s calculate the total amount of money earned per product.

    db.sales.aggregate([
      {
        $group: {
          _id: "$product",
          totalSales: { $sum: "$amount" }
        }
      }
    ]);

    { "_id": "Laptop", "totalSales": 210000 }
    { "_id": "Mouse", "totalSales": 1800 }
    { "_id": "Keyboard", "totalSales": 2500 }

Explanation: Here, we grouped by product and summed the amount field using $sum: "$amount".

This example calculates how many units of each product were sold.

    db.sales.aggregate([
      {
        $group: {
          _id: "$product",
          totalUnitsSold: { $sum: "$quantity" }
        }
      }
    ]);

    { "_id": "Laptop", "totalUnitsSold": 3 }
    { "_id": "Mouse", "totalUnitsSold": 3 }
    { "_id": "Keyboard", "totalUnitsSold": 1 }

Explanation: We grouped by product and added up the quantity for each.

Intuition Question

Q: What if a field (like quantity) is missing in some documents?

A: If a field is missing, MongoDB treats it as null, and $sum will ignore it unless it’s explicitly 0 or a number. To avoid surprises, validate your data before aggregation.

We can group by a combination of fields using embedded documents in _id.

    db.sales.aggregate([
      {
        $group: {
          _id: { customer: "$customer", product: "$product" },
          totalOrders: { $sum: 1 }
        }
      }
    ]);

    { "_id": { "customer": "Alice", "product": "Mouse" }, "totalOrders": 1 }
    { "_id": { "customer": "Alice", "product": "Laptop" }, "totalOrders": 1 }
    { "_id": { "customer": "Alice", "product": "Keyboard" }, "totalOrders": 1 }
    { "_id": { "customer": "Bob", "product": "Laptop" }, "totalOrders": 1 }
    { "_id": { "customer": "Bob", "product": "Mouse" }, "totalOrders": 1 }
    { "_id": { "customer": "Charlie", "product": "Laptop" }, "totalOrders": 1 }

Explanation: By using an object in _id, we create a grouped combination of customer and product. This helps answer "how many times did a customer buy a specific product?"

Choose a group key first, then select an accumulator. Counting records with $sum: 1 is different from adding a quantity field or computing price multiplied by quantity.

Summary

• $group is used to group documents by a field or fields
• $sum is used for counting documents or adding numeric values
• You can group by a single field or a combination of fields

These building blocks form the basis for more complex analytics with MongoDB’s aggregation pipeline.

• Review document filters