SELECT COUNT GROUP BY in MongoDB
- Operations in MongoDB
- Aggregate Operation
-
MongoDB
GROUP BY
andCOUNT
-
MongoDB
GROUP BY
,COUNT
,SORT
-
MongoDB
GROUP BY
andCOUNT
Multiple Fields -
MongoDB
GROUP BY
Date andCOUNT
-
MongoDB
GROUP BY
andCOUNT
With a Single Field -
MongoDB
GROUP BY
andCOUNT
With Multiple Fields -
MongoDB
GROUP BY
andCOUNT
With Sort Using Field With Multiple Fields -
MongoDB
GROUP BY
andCOUNT
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.
- The
Create
operation inserts new documents in the MongoDB database. - The
Read
operation queries a document in the database. - The
Update
operation modifies existing documents in the database. - 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.