👐 $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

JavaScript

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

C#

public class BookRating
{
    [BsonId]
    public string BookId { get; set; }

    [BsonElement("avgRating")]
    public double AvgRating { get; set; }
}

var pipeline = reviewsCollection.Aggregate()
    .Group(
        r => r.BookId,
        g => new BookRating
        {
            BookId = g.Key,
            AvgRating = g.Average(r => r.Rating.Value)
        }
    );

var booksWithAvg = pipeline.ToList();

👐 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

JavaScript

$group with $sum + $sort

await reviews.aggregate([
  {
    $group: {
      _id: "$name",
      totalReviews: { $sum: 1 },
    },
  },
  {
    $sort: {
      totalReviews: -1,
    },
  },
]).toArray();

$sortByCount

await reviews.aggregate([
  {
    $sortByCount: "$name",
  },
]).toArray();

C#

$group with $sum + $sort

public class ReviewerCount
{
  [BsonId]
  public string Name { get; set; }

  [BsonElement("totalReviews")]
  public int TotalReviews { get; set; }
}

var pipeline = reviewsCollection.Aggregate()
    .Group(
        r => r.Name,
        g => new ReviewerCount
        {
            Name = g.Key,
            TotalReviews = g.Count()
        }
    )
    .SortByDescending(r => r.TotalReviews);

var result = await pipeline.ToListAsync();

$sortByCount

var pipeline = reviewsCollection.Aggregate()
    .AppendStage<BsonDocument>(
        new BsonDocument("$sortByCount", "$name")
    );

var result = pipeline.ToList();