Skip to main content

👐 $group

In SQL, the GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country." The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

The $group stage in MongoDB’s Aggregation Framework is equivalent to GROUP BY in SQL. It allows us to group documents by a specific field and apply aggregate functions like sum, average, count, min, and max.

🎬 How does $group work?​

The $group stage groups documents based on a field and performs calculations on grouped data.

Syntax​

{
  $group: {
    _id: <expression>,
    <field>: { <accumulator>: <expression> }
  }
}
  • _id: The field to group by (use null to aggregate all documents together)
  • <accumulator>: An aggregation operator ($sum, $avg, $min, $max, $push, $addToSet, etc.)

👐 Example 1: Count the number of books published every year​

MongoDB query​

db.books.aggregate([
  {
    $group: {
      _id: "$year",
      totalBooks: { $sum: 1 },
    },
  },
]);

Equivalent SQL query​

SELECT year, COUNT(*) AS totalBooks
FROM books
GROUP BY year;

Sample output​

[
  {"_id": 1980,"totalBooks": 42},
  {"_id": 2000,"totalBooks": 490},
  {"_id": 1981,"totalBooks": 45},
  ...
]

👐 Example 2: Without using $group, count the number of books published every year​

MongoDB query​

db.books.aggregate([
  {
    $sortByCount: "$year",
  },
]);

Sample output remains the same as before​

[
  {"_id": 1980,"totalBooks": 42},
  {"_id": 2000,"totalBooks": 490},
  {"_id": 1981,"totalBooks": 45},
  ...
]

👐 Example 3: Find the total number of pages published every year.​

MongoDB query​

db.books.aggregate([
  {
    $group: {
      _id: "$year",
      totalPages: { $sum: "$pages" },
    },
  },
]);

Equivalent SQL query​

SELECT year, SUM(rating) AS totalPages
FROM books
GROUP BY year;

Sample output​

[
  { "_id": 1955, "totalPages": 664 },
  { "_id": 1952, "totalPages": 416 },
  { "_id": 1899, "totalPages": 128 }
  ...
]

👐 Challenge​

👐 1. Find the average book rating of all books​

Answer
db.reviews.aggregate([
  {
    $group: {
      _id: "$bookId",
      avgRating: { $avg: "$rating" }
    }
  },
]);

👐 2. Find users with the most number of reviews (Hint: use the name field in the reviews collection)​

Answer

There are 2 ways to solve this-

  • $group with $sort
  • $sortByCount
db.reviews.aggregate([
  {
    $group: {
      _id: "$name",
      totalReviews: { $sum: 1 },
    },
  },
  {
    $sort: {
      totalReviews: -1,
    },
  },
]);