SELECT COUNT GROUP BY in MongoDB

1. Operations in MongoDB
2. Aggregate Operation
3. MongoDB GROUP BY and COUNT
4. MongoDB GROUP BY, COUNT, SORT
5. MongoDB GROUP BY and COUNT Multiple Fields
6. MongoDB GROUP BY Date and COUNT
7. MongoDB GROUP BY and COUNT With a Single Field
8. MongoDB GROUP BY and COUNT With Multiple Fields
9. MongoDB GROUP BY and COUNT With Sort Using Field With Multiple Fields
10. MongoDB GROUP BY and COUNT With Sort With Multiple Fields

In this article, functions in MongoDB will be discussed. In addition, the aggregate function will be pointed out in detail.

We will explain different methods to count and sort multiple and single fields of the Group in MongoDB in detail.

Operations in MongoDB

The principles of a user interface that allow users to browse, search and alter elements of a database are referred to as CRUD operations.

Connecting to a server, querying the appropriate documents, modifying before sending the data back to the database to be processed, and changing setting properties are how MongoDB documents are amended.

CRUD is a data-driven process standardized using HTTP action verbs. Below are the CRUD operations and their uses.

1. The Create operation inserts new documents in the MongoDB database.
2. The Read operation queries a document in the database.
3. The Update operation modifies existing documents in the database.
4. The Delete operation removes documents in the database.

Aggregate Operation

It is a data processing operation consisting of stages that conduct several actions on grouped data to deliver a single output. Three ways to perform the aggregate operation are given below:

Aggregation Pipeline

Documents are fed into a multi-stage pipeline that combines them into a single output. There are several phases to the MongoDB aggregate process.

Example:

db.collection_name.aggregate([

   //First stage
   { $match: { status: "" } },

   //Second stage
   { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },

   //Third Stage
    { $sort : {sort_field: -1 }}

])

Single Purpose Aggregation Methods

Single-purpose aggregation methods are simple but lack the capabilities of an aggregation pipeline.

Map-Reduce Procedure

Starting with MongoDB 5.0, map-reduce procedures are deprecated. Use an aggregate pipeline instead.

MongoDB GROUP BY and COUNT

Each document’s _id field in MongoDB has been assigned a unique group by value. Then, the data is processed by the aggregate procedure, which delivers calculated results.

An example is given below. Here, you can see the configuration of the database.

This configuration will be used in all the examples of code provided in this article.

db={
    "data": [
    {
        "_id": ObjectId("611a99100a3322fc1bd8c38b"),
        "fname": "Tom",
        "city": "United States of America",
        "courses": [
            "c#",
            "asp",
            "node"
        ]
    },
    {
        "_id": ObjectId("611a99340a3322fc1bd8c38c"),
        "fname": "Harry",
        "city": "Canada",
        "courses": [
            "python",
            "asp",
            "node"
        ]
    },
    {
        "_id": ObjectId("611a99510a3322fc1bd8c38d"),
        "fname": "Mikky",
        "city": "New Zealand",
        "courses": [
            "python",
            "asp",
            "c++"
        ]
    },
    {
        "_id": ObjectId("611b3e88a60b5002406571c3"),
        "fname": "Ron",
        "city": "United Kingdom",
        "courses": [
            "python",
            "django",
            "node"
        ]
    }
    ]
}

Query for above database used is:

db.data.aggregate([
{
    $group: {
        _id: "ObjectId",
        count: {
            $count: {}
        }
    }
}
])

Check this link for the above execution to see the working of this code segment.

MongoDB GROUP BY, COUNT, SORT

$sortByCount is utilized in this group by the count sort topic, which is identical to $group Plus $sort. It may be used to sort and count a group in ascending and descending order.

Some documents are added to the data collection in the example below. The find() function is used to see how many records it has.

Query for find() will be:

db.data.find()

You can access the working of this query from this link.

The next step is to unwind the courses array and count the number of documents added to each course using the $sortByCount function. The query for this step is:

db.data.aggregate([
{
    $unwind: "$courses"
},
{
    $sortByCount: "$courses"
}
])

Click this link to see the working of this query with the above database configuration.

This is the most straightforward approach to MongoDB grouping, counting, and sorting arrays.

MongoDB GROUP BY and COUNT Multiple Fields

You can utilize the aggregate() method in MongoDB to count many fields. Therefore, $count is used to count the fields.

An example is given below in which a timestamp is used for only one entry. In this example, some papers can be saved in the student collection, and you can utilize the find() function to determine how many documents you have.

db.student.aggregate([ {$group: {_id: {name:"$name",
                                         timestamp:"$timestamp" }}},
                                        {$count:"timestamp"}
                      ])

The working of this query can be accessed by this link.

MongoDB GROUP BY Date and COUNT

You may use count aggregation and count a particular date document when you want to count a specific date document.

In the example below, you’ll learn to compute the overall sale amount and sale count for each day in 2021.

You can add the fields product id, item name, price, quantity, and date in the sales collection. The find() function may obtain the documents.

db=
{
        "_id" : 1,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 2,
        "date" : ISODate("2021-03-01T08:00:00Z")
}
{
        "_id" : 2,
        "item" : "jkl",
        "price" : NumberDecimal("20"),
        "quantity" : 1,
        "date" : ISODate("2021-03-01T09:00:00Z")
}
{
        "_id" : 3,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 10,
        "date" : ISODate("2021-03-15T09:00:00Z")
}
{
        "_id" : 4,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 20,
        "date" : ISODate("2021-04-04T11:21:39.736Z")
}
{
        "_id" : 5,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 10,
        "date" : ISODate("2021-04-04T21:23:13.331Z")
}

The query for the above configuration will be:

db.date.aggregate([
{
    $match : { "date": { $gte: new ISODate("2021-01-01"), $lt: new ISODate("2015-01-01") } }
},

{
    $group : {
        _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
        totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },

        count: { $sum: 1 }
    }
},
{
    $sort : { totalSaleAmount: -1 }
}
])

Now queries for single field groups and multiple field groups are given below using COUNT and FIELD commands are below.

MongoDB GROUP BY and COUNT With a Single Field

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

MongoDB GROUP BY and COUNT With Multiple Fields

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

MongoDB GROUP BY and COUNT With Sort Using Field With Multiple Fields

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

MongoDB GROUP BY and COUNT With Sort With Multiple Fields

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

This article discussed operations in detail, and aggregate operation was also discussed. First, different types of aggregate functions were discussed briefly with code segments.

Then GROUP BY and COUNT were discussed, including sorting, finding, and multiple fields. Then GROUP BY and the COUNT were discussed using field and count commands.