Convert SQL GROUP BY with COUNT to MongoDB $group with $sum

GROUP BY COUNT to $group $sum

SQL's GROUP BY with COUNT(*) groups rows and counts occurrences per group. In MongoDB, this requires the aggregate() pipeline with a $group stage.

Example SQL

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status

Generated MongoDB Query

db.orders.aggregate([
  {
    $group: {
      _id: "$status",
      total: { $sum: 1 },
      status: { $first: "$status" }
    }
  }
])

How $group Works

The $group stage is the MongoDB equivalent of SQL GROUP BY. The _id field specifies what to group by (prefixed with $ to reference a document field). Accumulator operators like $sum, $avg, $min, and $max compute values for each group.

COUNT(*) vs COUNT(column)

• COUNT(*) counts all rows, translated as { $sum: 1 } — adds 1 for each document in the group
• COUNT(column) counts non-NULL values, which in MongoDB would require a $match stage first to filter out null values, then { $sum: 1 }

Adding a Sort

To replicate ORDER BY total DESC, add a $sort stage after $group:

db.orders.aggregate([
  { $group: { _id: "$status", total: { $sum: 1 } } },
  { $sort: { total: -1 } }
])

HAVING Clause

SQL HAVING filters groups after aggregation. In MongoDB, this is a $match stage placed after the $group stage:

SELECT status, COUNT(*) AS total FROM orders GROUP BY status HAVING COUNT(*) > 10

db.orders.aggregate([
  { $group: { _id: "$status", total: { $sum: 1 } } },
  { $match: { total: { $gt: 10 } } }
])