MongoDB aggregation allows us to process documents, calculate totals, group records, and generate useful reports. In this lesson, we will create sales, product, department, and monthly revenue reports using the aggregation pipeline.
Suppose we have a sales database with the following collections:
sales — sales transaction recordsproducts — product detailsemployees — employee informationExample database and collection setup:
use salesDB
db.createCollection("sales")
db.createCollection("products")
db.createCollection("employees")Common aggregation stages include:
$match — filters documents.$group — groups documents and calculates totals.$project — selects or calculates output fields.$sort — sorts the results.$limit — limits the number of results.$lookup — combines matching documents from another collection.$unwind — separates array elements into individual documents.Insert sample sales data into the sales collection:
db.sales.insertMany([
{
sale_id: 1001,
product: "Laptop",
category: "Electronics",
quantity: 2,
price: 50000,
salesperson: "Rahul",
sale_date: ISODate("2026-01-10T00:00:00Z")
},
{
sale_id: 1002,
product: "Mouse",
category: "Accessories",
quantity: 5,
price: 500,
salesperson: "Priya",
sale_date: ISODate("2026-01-15T00:00:00Z")
},
{
sale_id: 1003,
product: "Laptop",
category: "Electronics",
quantity: 1,
price: 50000,
salesperson: "Rahul",
sale_date: ISODate("2026-02-05T00:00:00Z")
},
{
sale_id: 1004,
product: "Keyboard",
category: "Accessories",
quantity: 3,
price: 1500,
salesperson: "Amit",
sale_date: ISODate("2026-02-12T00:00:00Z")
},
{
sale_id: 1005,
product: "Monitor",
category: "Electronics",
quantity: 2,
price: 12000,
salesperson: "Priya",
sale_date: ISODate("2026-03-03T00:00:00Z")
},
{
sale_id: 1006,
product: "Mouse",
category: "Accessories",
quantity: 10,
price: 500,
salesperson: "Amit",
sale_date: ISODate("2026-03-20T00:00:00Z")
}
])
For this lesson, each document represents one sales
transaction. The transaction revenue is calculated
as quantity × price.
Run the sample insertion once to avoid adding duplicate sample records every time you practise.
Calculate the revenue for every transaction and then the overall total:
db.sales.aggregate([
{
$group: {
_id: null,
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
},
totalTransactions: {
$sum: 1
},
totalItemsSold: {
$sum: "$quantity"
}
}
},
{
$project: {
_id: 0,
totalRevenue: 1,
totalTransactions: 1,
totalItemsSold: 1
}
}
])Expected totals for the sample records:
_id: null groups all matching documents
into one group. $multiply calculates
revenue for each transaction, and $sum
adds the values together.
Find the total quantity sold and revenue for each product:
db.sales.aggregate([
{
$group: {
_id: "$product",
totalQuantity: {
$sum: "$quantity"
},
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
},
totalTransactions: {
$sum: 1
}
}
},
{
$sort: {
totalRevenue: -1
}
}
])Expected product revenue:
The report sorts products by revenue from highest to lowest. The total revenue across these products is ₹1,86,000.
Calculate revenue for each product category:
db.sales.aggregate([
{
$group: {
_id: "$category",
totalQuantity: {
$sum: "$quantity"
},
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
}
}
},
{
$sort: {
totalRevenue: -1
}
}
])Expected results:
This report helps a business compare the performance of different product categories.
Calculate revenue generated by each salesperson:
db.sales.aggregate([
{
$group: {
_id: "$salesperson",
totalTransactions: {
$sum: 1
},
totalQuantity: {
$sum: "$quantity"
},
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
},
averageTransactionValue: {
$avg: {
$multiply: ["$quantity", "$price"]
}
}
}
},
{
$sort: {
totalRevenue: -1
}
}
])Expected total revenue by salesperson:
Use $avg to calculate the average
transaction value for each salesperson.
Use $dateToString to group sales by
year and month.
db.sales.aggregate([
{
$group: {
_id: {
$dateToString: {
format: "%Y-%m",
date: "$sale_date",
timezone: "UTC"
}
},
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
},
totalTransactions: {
$sum: 1
}
}
},
{
$sort: {
_id: 1
}
}
])Expected monthly revenue:
The timezone is explicitly set to UTC because the sample dates use UTC. For business reports, select the timezone appropriate to your business.
Use $match to select transactions within
a date range before calculating revenue.
db.sales.aggregate([
{
$match: {
sale_date: {
$gte: ISODate("2026-01-01T00:00:00Z"),
$lt: ISODate("2026-03-01T00:00:00Z")
}
}
},
{
$group: {
_id: null,
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
},
totalTransactions: {
$sum: 1
}
}
}
])The query includes January and February 2026 but excludes March 1 and later dates.
Expected revenue for this date range: ₹1,57,000.
Calculate transaction revenue, sort it, and display the three highest-value transactions.
db.sales.aggregate([
{
$project: {
sale_id: 1,
product: 1,
salesperson: 1,
quantity: 1,
price: 1,
revenue: {
$multiply: ["$quantity", "$price"]
}
}
},
{
$sort: {
revenue: -1
}
},
{
$limit: 3
}
])
$project calculates the revenue field.
$sort places the highest values first,
and $limit returns only three documents.
Insert product details into the products collection:
db.products.insertMany([
{
product: "Laptop",
brand: "ExampleTech",
warranty_years: 2
},
{
product: "Mouse",
brand: "ExampleGear",
warranty_years: 1
},
{
product: "Keyboard",
brand: "ExampleGear",
warranty_years: 1
},
{
product: "Monitor",
brand: "ExampleView",
warranty_years: 3
}
])Combine sales with product information:
db.sales.aggregate([
{
$lookup: {
from: "products",
localField: "product",
foreignField: "product",
as: "product_details"
}
},
{
$project: {
_id: 0,
sale_id: 1,
product: 1,
quantity: 1,
product_details: 1
}
}
])
The $lookup stage matches the product
field in sales documents with the product field in
products documents.
In a production database, use a stable unique product identifier instead of a product name to avoid ambiguous matches.
Use $group to calculate product revenue
and $match to filter the grouped results.
db.sales.aggregate([
{
$group: {
_id: "$product",
totalRevenue: {
$sum: {
$multiply: ["$quantity", "$price"]
}
}
}
},
{
$match: {
totalRevenue: {
$gt: 10000
}
}
},
{
$sort: {
totalRevenue: -1
}
}
])Only products with total revenue greater than ₹10,000 will appear in the result.
Create an index on the sales date:
db.sales.createIndex({
sale_date: 1
})Check existing indexes:
db.sales.getIndexes()An index can improve date-range queries when MongoDB can use it effectively. Indexes consume storage and add work to writes, so create them according to actual query patterns.
salesDB.$lookup to combine sales and product details.sale_date.1. Which aggregation stage groups documents?
2. Which operator calculates the product of two numeric values?
3. Which aggregation stage combines matching documents from another collection?