Sling Academy
Home/MongoDB/MongoDB: Find SUM/MIN/MAX/AVG of each group in a collection

MongoDB: Find SUM/MIN/MAX/AVG of each group in a collection

Last updated: February 04, 2024

When working with large datasets in MongoDB, often there’s a need to compute various statistics, such as the sum, minimum, maximum, or average of specific properties across documents. This tutorial will guide you through performing these aggregations using MongoDB’s powerful aggregation framework. You’ll learn to effectively apply these operations within different groupings of your data.

Understanding Aggregation in MongoDB

MongoDB’s aggregation framework is a data processing pipeline that allows you to perform complex data transformations and analysis on your documents. It operates through a series of stages, each transforming the dataset in some manner, until the final desired result is achieved.

Setting Up Your Environment

Ensure you have MongoDB installed and running on your machine. This guide assumes you’re using MongoDB version 4.4 or later, which includes the necessary aggregation capabilities.

Calculating the SUM

Let’s start with a straightforward example. Suppose you have a collection named sales containing documents representing sales transactions. Here’s how you could find the total sales amount (sum) per product.

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

In this example, $group is an aggregation pipeline stage that groups the documents by the product field. The totalSales field in the output is calculated by summing up the amount field of all documents that share the same product.

Finding MIN/MAX within Groups

Next, let’s determine the minimum and maximum sale amount per product within the same collection.

db.sales.aggregate([
  {
    $group: {
      _id: '$product',
      minSale: { $min: '$amount' },
      maxSale: { $max: '$amount' }
    }
  }
])

This follows a similar pattern to the previous example but uses the $min and $max operators to find the lowest and highest sale amounts, respectively, for each product.

Calculating the Average

Now, for something slightly more complex, computing the average sale amount per product:

db.sales.aggregate([
  {
    $group: {
      _id: '$product',
      avgSale: { $avg: '$amount' }
    }
  }
])

The $avg operator accomplishes this, calculating the average of all amount values for each product grouping.

Advanced Grouping

Suppose your sales documents also include a date field, and you wish to aggregate sales not just by product, but also by year. MongoDB’s aggregation framework allows for this level of complexity.

db.sales.aggregate([
  {
    $group: {
      _id: { product: '$product', year: { $year: '$date' } },
      totalSales: { $sum: '$amount' }
    }
  }
])

This example groups sales by both product and the year the sale was made. The $year operator extracts the year part from the date field in each document.

Practical Application and Tips

Understanding how to perform these basic but powerful aggregations in MongoDB allows you to extract meaningful insights from your data. Here are a few tips to optimize your use of MongoDB’s aggregation framework:

  • Index fields that you frequently group by to improve performance.
  • Use the $match stage early in your pipeline to filter documents and reduce the data set size.
  • Consider the use of $project to reshape documents and reduce processing work in subsequent stages.

With these strategies and techniques, you’re well-equipped to perform efficient and powerful data aggregation tasks in MongoDB, enabling deeper analysis and insights from your data.

Next Article: MongoDB Upsert: Update if exists, otherwise insert new record

Previous Article: MongoDB Aggregation: MIN, MAX, SUM, AVG, COUNT (with examples)

Series: MongoDB Tutorials

MongoDB

You May Also Like

  • MongoDB: How to combine data from 2 collections into one
  • Hashed Indexes in MongoDB: A Practical Guide
  • Partitioning and Sharding in MongoDB: A Practical Guide (with Examples)
  • Geospatial Indexes in MongoDB: How to Speed Up Geospatial Queries
  • Understanding Partial Indexes in MongoDB
  • Exploring Sparse Indexes in MongoDB (with Examples)
  • Using Wildcard Indexes in MongoDB: An In-Depth Guide
  • Matching binary values in MongoDB: A practical guide (with examples)
  • Understanding $slice operator in MongoDB (with examples)
  • Caching in MongoDB: A practical guide (with examples)
  • CannotReuseObject Error: Attempted illegal reuse of a Mongo object in the same process space
  • How to perform cascade deletion in MongoDB (with examples)
  • MongoDB: Using $not and $nor operators to negate a query
  • References (Manual Linking) in MongoDB: A Developer’s Guide (with Examples)
  • MongoDB: How to see all fields in a collection (with examples)
  • Type checking in MongoDB: A practical guide (with examples)
  • How to query an array of subdocuments in MongoDB (with examples)
  • MongoDB: How to compare 2 documents (with examples)
  • MongoDB Connection String URI Format: Explained with Examples