Assignments Of Day 15: MongoDB Aggregation Operators
Assignment 1: Calculate Total Revenue from Orders
Objective:
Write an aggregation query to calculate the total revenue for all orders in a collection by summing the product of quantity and price.
Steps:
1. Create a collection orders with the following fields:
o orderId: The order identifier.
o quantity: The quantity of items in the order.
o price: The price per item.
2. Use $multiply and $sum operators within the $group stage to calculate total revenue.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: null,
totalRevenue: { $sum: { $multiply: ["$quantity", "$price"] } }
}
}
]);
Explanation:
- The $group operator groups all the documents together (_id: null), and $sum calculates the total revenue by multiplying quantity and price.
Assignment 2: Count the Number of Orders in Each Status
Objective:
Write an aggregation query to count the number of orders for each status in the collection.
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o status: The status of the order (e.g., "delivered", "pending").
2. Group by status and use $count to find the number of orders in each status.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$status",
orderCount: { $count: {} }
}
}
]);
Explanation:
- The $group operator groups the orders by status, and the $count operator counts the number of documents in each group.
Assignment 3: Average Salary by Department
Objective:
Write an aggregation query to calculate the average salary per department.
Steps:
1. Create a collection employees with fields:
o name: Employee name.
o department: Employee's department.
o salary: Employee's salary.
2. Use $group and $avg to calculate the average salary for each department.
Solution:
javascript
CopyEdit
db.employees.aggregate([
{
$group: {
_id: "$department",
avgSalary: { $avg: "$salary" }
}
}
]);
Explanation:
- $group groups the documents by department, and $avg calculates the average salary within each group.
Assignment 4: Group Employees by Department and Count
Objective:
Write an aggregation query to group employees by department and count how many employees belong to each department.
Steps:
1. Create a collection employees with fields:
o name: Employee name.
o department: Department name.
2. Group by department and use $count to count the employees.
Solution:
javascript
CopyEdit
db.employees.aggregate([
{
$group: {
_id: "$department",
employeeCount: { $count: {} }
}
}
]);
Explanation:
- The $group operator groups employees by department, and $count counts the number of employees in each group.
Assignment 5: Calculate Total Amount for Completed Orders
Objective:
Write an aggregation query to calculate the total amount of all orders where the status is "completed".
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o status: Status of the order (e.g., "completed", "pending").
o amount: The total price of the order.
2. Use $match and $sum to calculate the total amount of all completed orders.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{ $match: { status: "completed" } },
{ $group: { _id: null, totalAmount: { $sum: "$amount" } } }
]);
Explanation:
- The $match operator filters the documents where the status is "completed", and the $group operator sums the amount field for these documents.
Assignment 6: Average Order Amount per Customer
Objective:
Write an aggregation query to calculate the average order amount for each customer.
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o customerId: Customer identifier.
o amount: The total price of the order.
2. Use $group and $avg to calculate the average order amount per customer.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$customerId",
avgOrderAmount: { $avg: "$amount" }
}
}
]);
Explanation:
- The $group operator groups the orders by customerId, and $avg calculates the average amount for each customer.
Assignment 7: Total Revenue by Product Category
Objective:
Write an aggregation query to calculate the total revenue for each product category.
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o category: Product category.
o quantity: Quantity of items in the order.
o price: Price per item.
2. Group by category and calculate total revenue using $sum and $multiply.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$category",
totalRevenue: { $sum: { $multiply: ["$quantity", "$price"] } }
}
}
]);
Explanation:
- $group groups by category, and $sum with $multiply calculates the total revenue for each category.
Assignment 8: Count Orders by Year
Objective:
Write an aggregation query to count how many orders were placed in each year.
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o orderDate: Date of the order.
2. Use $project to extract the year from orderDate, then group by the year.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{ $project: { year: { $year: "$orderDate" } } },
{
$group: {
_id: "$year",
orderCount: { $count: {} }
}
}
]);
Explanation:
- $project extracts the year from orderDate using the $year operator, and $group counts the number of orders in each year.
Assignment 9: Average Order Value by Payment Method
Objective:
Write an aggregation query to calculate the average order value for each payment method.
Steps:
1. Create a collection orders with fields:
o orderId: Order identifier.
o paymentMethod: Payment method (e.g., "credit", "debit").
o amount: The total price of the order.
2. Group by paymentMethod and use $avg to calculate the average order amount.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$paymentMethod",
avgOrderValue: { $avg: "$amount" }
}
}
]);
Explanation:
- $group groups by paymentMethod, and $avg calculates the average order value for each payment method.
Assignment 10: Group Products by Category and Calculate Total Units Sold
Objective:
Write an aggregation query to group products by category and calculate the total units sold in each category.
Steps:
1. Create a collection sales with fields:
o productId: Product identifier.
o category: Product category.
o quantity: Quantity of products sold.
2. Group by category and use $sum to calculate the total quantity sold for each category.
Solution:
javascript
CopyEdit
db.sales.aggregate([
{
$group: {
_id: "$category",
totalUnitsSold: { $sum: "$quantity" }
}
}
]);
Explanation:
- $group groups by category, and $sum calculates the total quantity of products sold in each category.
These assignments will help students practice using MongoDB’s aggregation operators like $group, $sum, $avg, and $count to analyze and summarize data from different collections.
