Question

The name of my Collection is : People.

I am just mentioning the example of data of people:

> db.people.find().limit(1)

{ "_id" : ObjectId("5d7d87aafed1d209fd0ed58f"), "name" : { "last" : "Keyes", "first" : "Ella", "middle" : "Bella" }, "age" : 43, "gender" : "F", "address" : { "city" : "Lamesa", "state" : "TX" }, "skills" : [ "SQL", "Python", "Perl", "Julia" ] }

Could you please help me to solve below questions?

1) Find the number of people in each ten year age group (10 to 19, 20 to 29, etc.). Order the output by increasing age.

I have an answer for qeus-1, but it's not working!

db.people.aggregate([
       {"age":"$range: [ 0, 100, 10 ]" , count:{$sum:1}}
   ,
   {$sort:{"count":1}}
])

2)Count the number of people with each last name, print all last names and counts for which where is more than one person. Are there any duplicate first-last names in the collection?

Answer 1

1. We have the ages saved already. All we need to do is add a new field range and place each age group in the corresponding age range. And then have the number of counts for each age range. So let us break down the problem.

For adding the new field range, hard code each range set with appropriate labels. Here I have hardcoded for ages 0 to 90. After creating the range, simply group them by range and return the count. Also, add a sort option for sorting

  

db,people.aggregate([

{

$project:{

"range":{

$concat:[

{ $cond: [{$and:[ {$gt:["$age", 0 ]}, {$lt: ["$age", 10]}]}, "Under 10", ""] },

{ $cond: [{$and:[ {$gte:["$age", 10 ]}, {$lt: ["$age", 20]}]}, "10-20", ""] },

{ $cond: [{$and:[ {$gte:["$age", 20 ]}, {$lt: ["$age", 30]}]}, "20-30", ""] },

{ $cond: [{$and:[ {$gte:["$age", 30 ]}, {$lt: ["$age", 40]}]}, "30-40", ""] },

{ $cond: [{$and:[ {$gte:["$age", 40 ]}, {$lt: ["$age", 50]}]}, "40-50", ""] },

{ $cond: [{$and:[ {$gte:["$age", 50 ]}, {$lt: ["$age", 60]}]}, "50-60", ""] },

{ $cond: [{$and:[ {$gte:["$age", 60 ]}, {$lt: ["$age", 70]}]}, "60-70", ""] },

{ $cond: [{$and:[ {$gte:["$age", 70 ]}, {$lt: ["$age", 80]}]}, "70-80", ""] },

{ $cond: [{$and:[ {$gte:["$age", 80 ]}, {$lt: ["$age", 90]}]}, "80-90", ""] },

]

}

}

},

{$sort::{"age":1}}

{

  $group: {

    "_id" : "$range",

    count: {

      $sum: 1

    }

  }

}

])

2. Counting the number of people with respect to their last and first names is also the same way. Use aggregate to group using the last name and return the count.

db.people.aggregate([

{

  $group: {

    "_id" : "$last",

    count: {

      $sum: 1

    }

  }

}

])

To check for duplicate first names project the first names based on condition that the count >1.

db.people.aggregate([

$project:{

"first":{

{ $cond: {[count:{$gt:1},

$group: {

    "_id" : "$first",

    count: {

      $sum: 1

    }

  }

]}

}

}

}

])