1

I have a collection of customers, and I need to calculate the average age of the customers for each gender.

Customer data :

{"Id_Cust":"4145","firstName":"Albade","lastName":"Maazou","gender":"female","birthday":"21/03/1981","creationDate":"2010-03-13T02:10:23.099+0000","locationIP":"41.138.53.138","browserUsed":"Internet Explorer","place":"1263"},
{"Id_Cust":"5296","firstName":"Rafael","lastName":"Oliveira","gender":"male","birthday":"08/06/1987","creationDate":"2010-02-05T06:08:26.512+0000","locationIP":"192.160.128.234","browserUsed":"Internet Explorer","place":"574"},
{"Id_Cust":"6192","firstName":"Abdul Rahman","lastName":"Budjana","gender":"male","birthday":"26/01/1990","creationDate":"2010-02-08T20:32:23.706+0000","locationIP":"27.112.77.226","browserUsed":"Firefox","place":"640"},
{"Id_Cust":"6660","firstName":"Jerzy","lastName":"Ciesla","gender":"female","birthday":"28/12/1982","creationDate":"2010-03-23T11:02:30.998+0000","locationIP":"31.192.218.139","browserUsed":"Firefox","place":"1285"},
{"Id_Cust":"8491","firstName":"Chen","lastName":"Xu","gender":"female","birthday":"27/02/1985","creationDate":"2010-03-31T15:59:11.072+0000","locationIP":"1.1.7.155","browserUsed":"Chrome","place":"437"},
{"Id_Cust":"8664","firstName":"Andrej","lastName":"Benedejcic","gender":"female","birthday":"31/08/1988","creationDate":"2010-03-24T03:12:59.456+0000","locationIP":"90.157.195.42","browserUsed":"Firefox","place":"549"},
{"Id_Cust":"10027","firstName":"Ning","lastName":"Chen","gender":"female","birthday":"08/12/1982","creationDate":"2010-03-27T12:58:05.517+0000","locationIP":"1.2.9.86","browserUsed":"Firefox","place":"332"},
{"Id_Cust":"10664","firstName":"Emperor of Brazil","lastName":"Barreto","gender":"female","birthday":"02/02/1982","creationDate":"2010-03-04T09:43:10.090+0000","locationIP":"192.111.230.73","browserUsed":"Internet Explorer","place":"576"},
{"Id_Cust":"2199023256013","firstName":"Eli","lastName":"Peretz","gender":"female","birthday":"18/01/1989","creationDate":"2010-04-28T05:16:53.514+0000","locationIP":"193.194.1.47","browserUsed":"Internet Explorer","place":"1227"},

The result must be like this:

{ "_id" : "female" , "count" : 35}
{ "_id" : "male" , "count" : 35}

Note: 35 is just an example.

craigcaulfield
  • 3,381
  • 10
  • 32
  • 40
Maliari Mohammed
  • 57
  • 1
  • 8
  • Does this answer your question? [Convert date difference to years to calculate age in MongoDB](https://stackoverflow.com/questions/39381450/convert-date-difference-to-years-to-calculate-age-in-mongodb) – sidgate Dec 05 '19 at 10:05

2 Answers2

3

Taking age calculation purely based on years difference from birthday till today, following should do it:

db.tempColl.aggregate(
   [
     {
       $group:
         {
           _id: "$gender",
           count: { $avg: { $subtract: [ {$year: new Date()}, {$toInt:{$substr: ["$birthday", 6, -1]}} ] } }
         }
     }
   ]
);

This gives output as follows:

{ "_id" : "male", "count" : 30.5 }
{ "_id" : "female", "count" : 34.857142857142854 }

Note: You can use $ceil operator if you want abosolute numbers. Also this takes assumption that your birthday value is going be in th given format dd/mm/yyyy. And tempColl is your collection.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ambianBeing
  • 3,449
  • 2
  • 14
  • 25
  • it did not work i get this Error : `2019-07-28T22:58:07.760+0100 E QUERY [js] Error: command failed: { "ok" : 0, "errmsg" : "Failed to parse number '' in $convert with no onError value: No digits", "code" : 241, "codeName" : "ConversionFailure" } :` – Maliari Mohammed Jul 28 '19 at 21:59
  • May I know what version of **mongodb** are you using? Is the sample data correct in the question? Also a better answer than this from @mickl works too on the data you posted. – ambianBeing Jul 29 '19 at 02:23
  • the sample data in the question these are the first lines of my data, i have 9949 lines, but when I test the queries on the data in the questions it works. – Maliari Mohammed Jul 29 '19 at 09:32
1

Assuming the collection name is customer you can use the following aggregate query:

 db.customer.aggregate([
      {
        $project: {
          gender: 1,
          age: {
            $subtract: [
              {
                $year: ISODate()
              },
              {
                $year: {
                  $dateFromString: {
                    dateString: "$birthday",
                    format: "%d/%m/%Y"
                  }
                }
              }
            ]
          }
        }
      },
      {
        $group: {
          _id: "$gender",
          avgAge: {
            $avg: "$age"
          }
        }
      },
      {
        $project: {
          _id: 0,
          "gender": "$_id",
          "avgAge": 1,  
        }
      }
    ])

Output

[
  {
    "avgAge": 30.5,
    "gender": "male"
  },
  {
    "avgAge": 34.857142857142854,
    "gender": "female"
  }
]
MilanRegmi
  • 499
  • 3
  • 12