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 — maanijou
– maanijou, Commented Feb 10, 2021 at 9:29

varman · Accepted Answer · 2021-02-10 09:01:54Z

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, 2021 at 9:01

answered Feb 10, 2021 at 6:34

varman

8,9306 gold badges25 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Grug Over a year ago

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}]

varman Over a year ago

@Grug what is your expected result

Grug Over a year ago

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

varman Over a year ago

@Grug I have updated my answer, let me know if you need help

prasad_ · Accepted Answer · 2021-02-10 09:42:52Z

0

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

answered Feb 10, 2021 at 9:42

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related