Assignments Of Day 10: Aggregation Framework – Part 1 (Basics)
1. Sum of Total Quantity Sold
Problem:
Create a collection sales with fields product, quantity, and price. Calculate the total quantity sold for all products.
Step-by-step solution:
1. Create the sales collection:
javascript
CopyEdit
db.sales.insertMany([
{ product: "Laptop", quantity: 5, price: 50000 },
{ product: "Phone", quantity: 10, price: 20000 },
{ product: "Tablet", quantity: 7, price: 15000 }
]);
2. Aggregation pipeline to calculate total quantity sold:
javascript
CopyEdit
db.sales.aggregate([
{
$group: {
_id: null, // Grouping all documents
totalQuantity: { $sum: "$quantity" } // Summing the 'quantity' field
}
}
]);
3. Expected Output:
json
CopyEdit
{ "_id": null, "totalQuantity": 22 }
2. Average Price of Products
Problem:
Create a collection products with fields productId, productName, and price. Calculate the average price of all products.
Step-by-step solution:
1. Create the products collection:
javascript
CopyEdit
db.products.insertMany([
{ productId: 1, productName: "Laptop", price: 50000 },
{ productId: 2, productName: "Phone", price: 20000 },
{ productId: 3, productName: "Tablet", price: 15000 }
]);
2. Aggregation pipeline to calculate average price:
javascript
CopyEdit
db.products.aggregate([
{
$group: {
_id: null, // Grouping all documents
averagePrice: { $avg: "$price" } // Calculating the average of 'price'
}
}
]);
3. Expected Output:
json
CopyEdit
{ "_id": null, "averagePrice": 28333.33 }
3. Group Products by Category
Problem:
Create a collection inventory with fields productId, category, and stockQuantity. Group products by category and calculate the total stock for each category.
Step-by-step solution:
1. Create the inventory collection:
javascript
CopyEdit
db.inventory.insertMany([
{ productId: 1, category: "Electronics", stockQuantity: 50 },
{ productId: 2, category: "Clothing", stockQuantity: 100 },
{ productId: 3, category: "Electronics", stockQuantity: 30 }
]);
2. Aggregation pipeline to group by category and sum stock:
javascript
CopyEdit
db.inventory.aggregate([
{
$group: {
_id: "$category", // Group by 'category'
totalStock: { $sum: "$stockQuantity" } // Sum stock quantities
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": "Electronics", "totalStock": 80 },
{ "_id": "Clothing", "totalStock": 100 }
]
4. Find the Highest Price Product
Problem:
Create a collection products with fields productId, productName, and price. Find the product with the highest price.
Step-by-step solution:
1. Create the products collection:
javascript
CopyEdit
db.products.insertMany([
{ productId: 1, productName: "Laptop", price: 50000 },
{ productId: 2, productName: "Phone", price: 20000 },
{ productId: 3, productName: "Tablet", price: 15000 }
]);
2. Aggregation pipeline to find the highest price:
javascript
CopyEdit
db.products.aggregate([
{
$sort: { price: -1 } // Sort products by price in descending order
},
{ $limit: 1 } // Limit to the top product
]);
3. Expected Output:
json
CopyEdit
{ "_id": 1, "productName": "Laptop", "price": 50000 }
5. Count the Number of Products in Each Category
Problem:
Create a collection products with fields productId, productName, and category. Count how many products are there in each category.
Step-by-step solution:
1. Create the products collection:
javascript
CopyEdit
db.products.insertMany([
{ productId: 1, productName: "Laptop", category: "Electronics" },
{ productId: 2, productName: "Phone", category: "Electronics" },
{ productId: 3, productName: "Shirt", category: "Clothing" }
]);
2. Aggregation pipeline to count products per category:
javascript
CopyEdit
db.products.aggregate([
{
$group: {
_id: "$category", // Group by 'category'
productCount: { $count: {} } // Count the number of products in each category
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": "Electronics", "productCount": 2 },
{ "_id": "Clothing", "productCount": 1 }
]
6. Find the Average Salary by Department
Problem:
Create a collection employees with fields employeeId, name, department, and salary. Calculate the average salary by department.
Step-by-step solution:
1. Create the employees collection:
javascript
CopyEdit
db.employees.insertMany([
{ employeeId: 1, name: "John", department: "Sales", salary: 50000 },
{ employeeId: 2, name: "Emma", department: "Sales", salary: 60000 },
{ employeeId: 3, name: "Sophia", department: "HR", salary: 40000 }
]);
2. Aggregation pipeline to calculate average salary by department:
javascript
CopyEdit
db.employees.aggregate([
{
$group: {
_id: "$department", // Group by 'department'
avgSalary: { $avg: "$salary" } // Calculate the average salary
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": "Sales", "avgSalary": 55000 },
{ "_id": "HR", "avgSalary": 40000 }
]
7. Group Orders by Customer
Problem:
Create a collection orders with fields orderId, customerId, amount. Group orders by customerId and calculate the total order amount for each customer.
Step-by-step solution:
1. Create the orders collection:
javascript
CopyEdit
db.orders.insertMany([
{ orderId: 1, customerId: "C001", amount: 500 },
{ orderId: 2, customerId: "C002", amount: 700 },
{ orderId: 3, customerId: "C001", amount: 300 }
]);
2. Aggregation pipeline to group orders by customerId and calculate total amount:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$customerId", // Group by 'customerId'
totalAmount: { $sum: "$amount" } // Sum the 'amount' field for each customer
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": "C001", "totalAmount": 800 },
{ "_id": "C002", "totalAmount": 700 }
]
8. Calculate the Minimum Salary in the Company
Problem:
Create a collection employees with fields employeeId, name, salary. Calculate the minimum salary in the company.
Step-by-step solution:
1. Create the employees collection:
javascript
CopyEdit
db.employees.insertMany([
{ employeeId: 1, name: "John", salary: 50000 },
{ employeeId: 2, name: "Emma", salary: 60000 },
{ employeeId: 3, name: "Sophia", salary: 40000 }
]);
2. Aggregation pipeline to calculate the minimum salary:
javascript
CopyEdit
db.employees.aggregate([
{
$group: {
_id: null, // Grouping all documents together
minSalary: { $min: "$salary" } // Calculate the minimum salary
}
}
]);
3. Expected Output:
json
CopyEdit
{ "_id": null, "minSalary": 40000 }
9. Group by Product and Count Orders
Problem:
Create a collection orders with fields orderId, product, and quantity. Group by product and count the number of orders for each product.
Step-by-step solution:
1. Create the orders collection:
javascript
CopyEdit
db.orders.insertMany([
{ orderId: 1, product: "Laptop", quantity: 2 },
{ orderId: 2, product: "Phone", quantity: 3 },
{ orderId: 3, product: "Laptop", quantity: 1 }
]);
2. Aggregation pipeline to group by product and count orders:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$product", // Group by 'product'
orderCount: { $count: {} } // Count the number of orders for each product
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": "Laptop", "orderCount": 2 },
{ "_id": "Phone", "orderCount": 1 }
]
10. Calculate the Total Sales for Each Product
Problem:
Create a collection sales with fields product, quantity, and price. Calculate the total sales for each product (i.e., quantity * price).
Step-by-step solution:
1. Create the sales collection:
javascript
CopyEdit
db.sales.insertMany([
{ product: "Laptop", quantity: 5, price: 50000 },
{ product: "Phone", quantity: 10, price: 20000 },
{ product: "Tablet", quantity: 7, price: 15000 }
]);
2. Aggregation pipeline to calculate total sales for each product:
javascript
CopyEdit
db.sales.aggregate([
{
$project: {
product: 1, // Include the 'product' field
totalSales: { $multiply: ["$quantity", "$price"] } // Calculate total sales for each product
}
}
]);
3. Expected Output:
json
CopyEdit
[
{ "_id": ObjectId("..."), "product": "Laptop", "totalSales": 250000 },
{ "_id": ObjectId("..."), "product": "Phone", "totalSales": 200000 },
{ "_id": ObjectId("..."), "product": "Tablet", "totalSales": 105000 }
]
