SELECT COUNT GROUP BY in MongoDB

To count documents by a field, a combination of fields, or a date, use a MongoDB aggregation pipeline. Its ordered stages transform documents; $group sets each output group’s _id, and $sum: 1 counts the input documents in that group. Add $unwind before counting array elements, and $sort to order the results.

The examples below use a students collection with name, city, and courses fields, plus a separate sales collection for the date example. Replace these names with the collection and fields in your data.

Count documents by one field with $group

Group on the field whose distinct values you want to see. For example, db.students.aggregate([{$group: {_id: "$city", count: {$sum: 1}}}]) produces one result per city. Each result has the city value in _id and its document count in count. $sum: 1 adds one for every document entering the group; it does not count distinct values of another field.

A compact model of this counting pattern, using ordinary Python values, is shown here. It is a language-level illustration, not a MongoDB query or runtime evidence.

Run this example:

students = [{"city": "Canada"}, {"city": "Canada"}, {"city": "Japan"}]
counts = {}
for student in students:
    city = student["city"]
    counts[city] = counts.get(city, 0) + 1

The resulting mapping has Canada mapped to 2 and Japan mapped to 1. In MongoDB, the equivalent group key is the field path "$city".

Count documents by multiple fields

To count each combination, make _id an object containing every grouping field. For example, db.students.aggregate([{$group: {_id: {city: "$city", name: "$name"}, count: {$sum: 1}}}]) counts documents for each city-and-name pair. MongoDB treats the whole _id object as the group key, so changing either value creates a different group. Add or remove properties in _id to change the combinations without changing the count expression.

Use $sum: 1 inside $group for document counts. The $count stage instead counts the documents that reach that stage and returns a count field; it does not replace the accumulator when each group needs its own count.

Count array values with $unwind and $sortByCount

An array-valued field needs to be turned into individual values before counting its members. The pipeline db.students.aggregate([{$unwind: "$courses"}, {$sortByCount: "$courses"}]) emits one pipeline document for each course element, then groups equal course values and sorts the counts from highest to lowest. A student with three courses contributes once to each of those three course counts.

$sortByCount is shorthand for grouping by an expression with a $sum: 1 accumulator and sorting by that count in descending order. It counts occurrences, not unique students per course if the same course appears more than once in a student’s array. If that distinction matters, deduplicate the array before unwinding or construct a pipeline that groups by both student and course first.

Sort grouped counts

Add $sort after $group to choose the order explicitly. For the single-field city result, {$sort: {_id: 1}} sorts city keys ascending and {$sort: {_id: -1}} sorts them descending. For compound groups, sort a nested key such as {"_id.city": 1}. To rank by frequency, use {$sort: {count: -1}}; 1 means ascending and -1 means descending.

For example, db.students.aggregate([{$group: {_id: "$city", count: {$sum: 1}}}, {$sort: {count: -1, _id: 1}}]) ranks the largest groups first and uses the city key to break ties. Sorting by a field that is not in the grouped output will not provide the intended ordering, so sort on _id or an accumulator such as count.

Group dates and calculate counts and totals

Filter the intended date interval before grouping, normalize each timestamp to a calendar day, and calculate both a count and a total in $group. For UTC days in 2021, an illustrative pipeline is db.sales.aggregate([{$match: {date: {$gte: ISODate("2021-01-01T00:00:00Z"), $lt: ISODate("2022-01-01T00:00:00Z")}}}, {$group: {_id: {$dateToString: {format: "%Y-%m-%d", date: "$date", timezone: "UTC"}}, count: {$sum: 1}, totalSales: {$sum: {$multiply: ["$price", "$quantity"]}}}}, {$sort: {_id: 1}}]).

The lower bound is inclusive and the next year’s start is exclusive, so timestamps throughout 2021 are included while 2022 is excluded. $dateToString puts timestamps from the same UTC day into one group. $sum: 1 counts sales documents, while $multiply calculates each line’s price times quantity before $sum adds those amounts. Use the timezone that matches the reporting day; a business-local day may not align with UTC. Ensure date, price, and quantity have suitable date and numeric values, and change the interval to match the period you need.