How to perform count(column) in mongodb with aggregation?

Question

I am trying to do to the equivalent of the following query in mongodb:

select count(*), count(category), sum(price) from sales group by usergender

Here is what the documents in my collection look like:

{
    "_id" : ObjectId("54da8b0aa7c80aed4a9f9f33"),
    "userincome" : "$100,000 - $200,000",
    "county" : "Los Angeles",
    "userstate" : "California",
    "usercity" : "Los Angeles",
    "price" : 100,
    "category" : "Swimwear",
    "usergender" : "Male"
}

Here is my aggregation which returns count(*) and sum(price) but I am not sure how to add in count(category).

db['stream.sales'].aggregate([  
   {  
      $group:{  
         _id:"$usergender",
         price:{  
            $sum:"$price"
         },
         _count:{  
            $sum:1
         }
      }
   }
])

I know I can run a separate aggregation to get count(category) but I would like to do it in aggregation, because I don't want all my results filtered where category exists = true.

db['stream.sales'].aggregate([  
   {  
      $match:{  
         'category':{  
            "$exists":true
         }
      }
   },
   {  
      $group:{  
         _id:"$usergender",
         count:{  
            $sum:1
         }
      }
   }
]);

Edit:

Was able to find the solution with the help of wdberkleys response:

  db['stream.sales'].aggregate([
        { "$group" : { 
            "_id" : "$usergender",
            "count" : { "$sum" : 1 },
            "price" : { "$sum" : "$price" },
            "category" : { "$push" : "$category" }
        } },
        { "$project" : {
            "count" : 1,
            "size" : 1,
            "categories" : { "$size" : "$category" } 
        } }
    ])

wdberkeley · Accepted Answer · 2015-02-12 18:56:52Z

2

Push the categories to a set during the $group, then $project the size of the resulting set of categories:

db.stream.sales.aggregate([
    { "$group" : { 
        "_id" : "$usergender",
        "count" : { "$sum" : 1 },
        "price" : { "$sum" : "$price" },
        "categories" : { "$addToSet" : "$category" }
    } },
    { "$project" : {
        "count" : 1,
        "size" : 1,
        "categories" : { "$size" : "$category" } 
    } }
])

answered Feb 12, 2015 at 18:56

wdberkeley

11.7k1 gold badge30 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheTurkish Over a year ago

This returns a count(distinct(column)). What I wanted was a count(column). However, this put me in the track and I was able to find the solution which I have added to my question.

wdberkeley Over a year ago

Good. Sorry about the error - I have never actually used SQL before so I mistranslated the statement.

Collectives™ on Stack Overflow

How to perform count(column) in mongodb with aggregation?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related