How to write one query (count distinct, sum) in MongoDB?

Question

Query: select count(distinct finish_date), sum(study_num) from table where student_id=1234

Documents:

{
    "_id" : ObjectId("602252684a43d5b364f3e6ca"),
    "student_id" : 1234,
    "study_num" : 8,
    "finish_date" : "20210209",
},
{
    "_id" : ObjectId("602257594a43d5b364f4cc6a"),
    "student_id" : 1234,
    "study_num" : 7,
    "finish_date" : "20210207",
},
{
    "_id" : ObjectId("5fbb65580d685b17fa56e18f"),
    "student_id" : 2247,
    "study_num" : 6,
    "finish_date" : "20210209",
}

Does this answer your question? [mongodb count num of distinct values per field/key](https://stackoverflow.com/questions/14924495/mongodb-count-num-of-distinct-values-per-field-key) — maanijou, Feb 10 '21 at 09:29

varman · Answer 1 · 2021-02-10T09:01:54.790

0

You can use $match and $group

db.collection.aggregate([
  {
    "$match": {"student_id": 1234}
  },
  {
    "$group": {
      "_id": "$finish_date",
      "study_sum": { $sum: "$study_num" }
    }
  },
  {
    "$group": {
      "_id": null,
      "study_sum": { $sum: "$study_sum" },
      count: { $sum: 1 }
    }
  }
])

Working Mongo playground

edited Feb 10 '21 at 09:01

answered Feb 10 '21 at 06:34

varman

8,704
5
19
53

Your answer's result is not i want. Your result:[{"study_sum":8,"count":1},{"study_sum":7,"count":1}]. I want:[{"study_sum":15,"count":2}] – Grug Feb 10 '21 at 07:43
@Grug what is your expected result – varman Feb 10 '21 at 07:44
your updated answer's "study_sum" is right, but "count" is not distinct. if i modify "20210207" to "20210209", your answer's "count" is 2, but the expected is 1 – Grug Feb 10 '21 at 08:13
@Grug I have updated my answer, let me know if you need help – varman Feb 10 '21 at 09:02

score 0 · Answer 2 · answered Feb 10 '21 at 09:42

Query: select count(distinct finish_date), sum(study_num) from table where student_id=1234

How to write the query? Write using an aggregation:

db.collection.aggregate([
  { 
    $match: { student_id: 1234 } 
  },
  { 
    $group: {
         _id: "", 
         distinct_dates: { $addToSet: "$finish_date"  }, 
         study_sum: { $sum: "$study_num" } 
    } 
  },
  { 
    $project: { 
        count: { $size: "$distinct_dates" }, 
        study_sum: 1, _id: 0 
    } 
  }
])

The output: { "study_sum" : 15, "count" : 2 }

Reference: SQL to Aggregation Mapping Chart

How to write one query (count distinct, sum) in MongoDB?

2 Answers2