Convert SQL GROUP BY with SUM to MongoDB $group with $sum Field

GROUP BY SUM to $group $sum

While COUNT adds 1 per document, SUM adds the actual field value. The MongoDB $sum accumulator handles both cases — the difference is whether you pass 1 or a field reference.

Example SQL

SELECT category, SUM(amount) AS total_amount
FROM transactions
GROUP BY category

Generated MongoDB Query

db.transactions.aggregate([
  {
    $group: {
      _id: "$category",
      total_amount: { $sum: "$amount" },
      category: { $first: "$category" }
    }
  }
])

$sum with Field Reference

The key difference from COUNT is the $sum argument:

• { $sum: 1 } — counts documents (equivalent to COUNT(*))
• { $sum: "$amount" } — sums the amount field values (equivalent to SUM(amount))

The $ prefix tells MongoDB to reference the field's value rather than treating it as a literal string.

Multiple Aggregations

SQL allows multiple aggregate functions in a single query:

SELECT category, SUM(amount) AS total, COUNT(*) AS count
FROM transactions
GROUP BY category

db.transactions.aggregate([
  {
    $group: {
      _id: "$category",
      total: { $sum: "$amount" },
      count: { $sum: 1 },
      category: { $first: "$category" }
    }
  }
])

Handling NULL Values

In SQL, SUM ignores NULL values. MongoDB's $sum ignores documents where the field is missing or null, producing the same behavior. However, if the field contains non-numeric values, MongoDB will skip them rather than raising an error, unlike SQL which would fail.

Decimal Precision

For financial calculations, be aware that MongoDB uses IEEE 754 floating-point by default. Use the Decimal128 type for exact decimal arithmetic, similar to SQL's DECIMAL / NUMERIC types.