MongoDB SELECT COUNT GROUP BY

Question

I am playing around with MongoDB trying to figure out how to do a simple

SELECT province, COUNT(*) FROM contest GROUP BY province

But I can't seem to figure it out using the aggregate function. I can do it using some really weird group syntax

db.user.group({
    "key": {
        "province": true
    },
    "initial": {
        "count": 0
    },
    "reduce": function(obj, prev) {
        if (true != null) if (true instanceof Array) prev.count += true.length;
        else prev.count++;
    }
});

But is there an easier/faster way using the aggregate function?

Steven · Accepted Answer · 2014-04-16 17:47:31Z

582

This would be the easier way to do it using aggregate:

db.contest.aggregate([
    {"$group" : {_id:"$province", count:{$sum:1}}}
])

edited Apr 16, 2014 at 17:47

Steven

14.1k35 gold badges103 silver badges155 bronze badges

answered Apr 16, 2014 at 17:39

Anand Jayabalan

13.1k6 gold badges43 silver badges54 bronze badges

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

6 Comments

Steven Over a year ago

I get an error message when I try that "errmsg" : "exception: A pipeline stage specification object must contain exactly one field.", ?

Filip Bartuzi Over a year ago

how do you group sort to it? I want to sort count by -1

elaich Over a year ago

@FilipBartuzi there is an example in the documentation page, you'll have to add a sort operation to the pipeline, as { $sort: { count: -1 } }

Peter Perháč Over a year ago

I got the same exception as @Steven and it was because I copy-pasted just line 2 and omitted the surrounding square brackets.

BivorAdrito Over a year ago

@Steven you will have to execute it properly use something like this .aggregate(aggregate).exec();

|

csharpbd · Accepted Answer · 2017-04-12 18:52:43Z

I need some extra operation based on the result of aggregate function. Finally I've found some solution for aggregate function and the operation based on the result in MongoDB. I've a collection Request with field request, source, status, requestDate.

Single Field Group By & Count:

db.Request.aggregate([
    {"$group" : {_id:"$source", count:{$sum:1}}}
])

Multiple Fields Group By & Count:

db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}}
])

Multiple Fields Group By & Count with Sort using Field:

db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}},
    {$sort:{"_id.source":1}}
])

Multiple Fields Group By & Count with Sort using Count:

db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}},
    {$sort:{"count":-1}}
])

can you explain {_id:{source:"$source",status:"$status"} this?
Basically, the field "_id" is a unique identifier for each document. The field accepts an expression. You can define the value of the field by combining multiple fields based on your grouping criteria. You will find more details about the field in the link: docs.mongodb.com/manual/reference/operator/aggregation/group/…

EugenSunic · Accepted Answer · 2018-12-09 15:11:44Z

76

If you need multiple columns to group by, follow this model. Here I am conducting a count by status and type:

  db.BusinessProcess.aggregate({
    "$group": {
        _id: {
            status: "$status",
            type: "$type"
        },
        count: {
            $sum: 1
        }
    }
   })

edited Dec 9, 2018 at 15:11

EugenSunic

13.8k15 gold badges66 silver badges96 bronze badges

answered Jul 2, 2015 at 15:58

KitkatJohn

7695 silver badges2 bronze badges

2 Comments

EugenSunic Over a year ago

_id represents a default param for encapsulating multiple fields?

csharpbd Over a year ago

@RoyiNamir, please take a look at the link. You may find your information there. docs.mongodb.com/manual/reference/operator/aggregation/group/…

MattM · Accepted Answer · 2019-01-28 19:06:34Z

59

Starting in MongoDB 3.4, you can use the $sortByCount aggregation.

Groups incoming documents based on the value of a specified expression, then computes the count of documents in each distinct group.

https://docs.mongodb.com/manual/reference/operator/aggregation/sortByCount/

For example:

db.contest.aggregate([
    { $sortByCount: "$province" }
]);

answered Jan 28, 2019 at 19:06

MattM

1,2891 gold badge14 silver badges26 bronze badges

1 Comment

Neil Lunn Over a year ago

Probably worth noting here that $sortByCount is actually a "pseudo operator" like several more aggregation stage operators introduced from MongoDB 3.4. All they really do is expand into their respective aggregation stages. In this case a $group with $sum: 1 as shown in existing answers and an additional $sort stage. They offer no advantage other than "typing less code", which may or may not be more descriptive ( if you're into that sort of thing ). IMHO, distinct $group and $sort stages in the code are far more descriptive and indeed more flexible.

andre · Accepted Answer · 2018-01-11 17:09:05Z

29

Additionally if you need to restrict the grouping you can use:

db.events.aggregate( 
    {$match: {province: "ON"}},
    {$group: {_id: "$date", number: {$sum: 1}}}  
)

answered Jan 11, 2018 at 17:09

andre

1,24212 silver badges14 bronze badges

Comments

prule · Accepted Answer · 2014-11-29 18:39:42Z

12

This type of query worked for me:

 db.events.aggregate({$group: {_id : "$date", number:  { $sum : 1} }} )

See http://docs.mongodb.org/manual/tutorial/aggregation-with-user-preference-data/

answered Nov 29, 2014 at 18:39

prule

2,58432 silver badges33 bronze badges

Comments

Xavier Guihot · Accepted Answer · 2021-07-13 16:25:38Z

8

Starting in Mongo 5.0, we can also use { $count: { } } as an alias for { $sum : 1 }:

// { "province" : "Champagne-Ardenne" }
// { "province" : "Champagne-Ardenne" }
// { "province" : "Haute-Normandie"   }
db.collection.aggregate([
  { $group: { _id: "$province", count: { $count: {} } } }
])
// { "_id" : "Champagne-Ardenne", "count" : 2 }
// { "_id" : "Haute-Normandie",   "count" : 1 }

edited Jul 13, 2021 at 16:25

answered Jun 15, 2021 at 20:09

Xavier Guihot

62.6k26 gold badges320 silver badges202 bronze badges

Comments

Kaushik Das · Accepted Answer · 2021-03-16 09:45:02Z

    db.contest.aggregate([
        { $match:{.....May be some match criteria...}},
        { $project: {"province":1,_id:0}},
        { $sortByCount: "$province" }
    ],{allowDiskUse:true});

MongoDB have 32 MB limitation of sorting operation on memory, use allowDiskUse : true this option, when you expose this query upfront of millions of data, it will sort at disk level not in memory. MongoDB aggregation pipeline has 100MB limitation, so use $project to reduce the data flowing to next pipeline. If you are using small data then no need to use allowDiskUse option.

Riha · Accepted Answer · 2019-10-30 06:51:34Z

-1

Mongo shell command that worked for me:

db.getCollection(<collection_name>).aggregate([{"$match": {'<key>': '<value to match>'}}, {"$group": {'_id': {'<group_by_attribute>': "$group_by_attribute"}}}])

edited Oct 30, 2019 at 6:51

answered Oct 30, 2019 at 6:42

Riha

91 bronze badge

Collectives™ on Stack Overflow

MongoDB SELECT COUNT GROUP BY

9 Answers 9

6 Comments

2 Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

6 Comments

2 Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Related