The below mentioned is the Collection used:
[
{
_id: 1,
joinedAt:ISODate("2021-07-20T12:31:33.229+05:30"),
firstName: "John",
lastName: "King",
salary: 5000,
department: {
"name": "HR"
}
},
{
_id: 2,
joinedAt:ISODate("2021-08-20T12:31:33.229+05:30"),
firstName: "Sachin",
lastName: "T",
salary: 8000,
department: {
"name": "Marketing"
}
},
{
_id: 3,
joinedAt:ISODate("2021-06-20T12:31:33.229+05:30"),
firstName: "James",
lastName: "Bond",
salary: 7500,
department: {
"name": "Marketing"
}
},
{
_id: 4,
joinedAt:ISODate("2021-05-20T12:31:33.229+05:30"),
firstName: "Rosy",
lastName: "Brown",
salary: 5000,
department: {
"name": "HR"
}
},
{
_id: 5,
joinedAt:ISODate("2021-07-26T12:31:33.229+05:30"),
firstName: "Kapil",
lastName: "D",
salary: 4500,
department: {
"name": "HR"
}
},
{
_id: 6,
joinedAt:ISODate("2021-07-20T12:31:33.229+05:30"),
firstName: "Amitabh",
lastName: "B",
salary: 7000,
department: {
"name": "Marketing"
}
}
]
I used the following query:
db.collections.aggregate([
{
$match:
{ createdAt:
{
$gte: ISODate("2021-01-01T17:06:02.713+05:30"),
$lte: ISODate("2021-12-31T17:06:02.713+05:30")
}
}
},
{$sort: {createdAt: 1}},
{ $group:{ _id:{department:'$department.name'}, totalEmployees:
{$sum:1},firstEmployee: {$first: "$firstName"} }
}])
Using this query I can get the count of employees in each department and the first name of the employee who joined first in each department. I would also like to get the count of employees joined in each department in a particular month(eg: employees joined HR department in September). What are the changes to be made in this query?