0

I've been trying to get my head around aggregation for a while now and I can't seem to work out how to find the average, min or max, of a sum of strings.

    db.mycollectionname.aggregate([
    {$unwind: "$Monitor"},
        {$group: {_id: "$Monitor.Mon Type",
        "Total": {$sum: 1}
        }
    }
])

I know it's not much, but this is how far I managed to get, it pumps out this;

{ "_id" : "RM", "Total" : 21 }
{ "_id" : "PT", "Total" : 43 }
{ "_id" : "IM", "Total" : 24 }
{ "_id" : "IO", "Total" : 72 }

What I'm trying to do is get the min/max of these sum results and an average of all the results. Any help or advice is appreciated, can't seem to find anything that helps me out. Thank you

Sppicy
  • 3
  • 1

1 Answers1

2

Add this stage as last stage :

{$group :{_id:'', minimum :{$min: '$Total'}, maximum :{$max: '$Total'}, Total :{$sum : '$Total'}, average :{$avg : '$Total'}}}

So your query :

db.mycollectionname.aggregate([
    { $unwind: "$Monitor" },
    {
        $group: {
            _id: "$Monitor.Mon Type",
            "Total": { $sum: 1 }
        }
    },
    {
        $group: {
            _id: '', minimum: { $min: '$Total' }, maximum: { $max: '$Total' },
            Total: { $sum: '$Total' }, average: { $avg: '$Total' }
        }
    }
])

So when you do _id: '' or _id: null, a group stage would iterate through all the documents at that point. After adding final $group stage result should be :

/* 1 */
{
    "_id" : "",
    "minimum" : 21.0,
    "maximum" : 72.0,
    "Total" : 160.0,
    "average" : 40.0
}
whoami - fakeFaceTrueSoul
  • 17,086
  • 6
  • 32
  • 46
  • Sorry to bother you again, but when I run the query, I get the error message, 'The field 'Total' must be an accumulator object', am I missing anything in the query or is it an issue related to my data? I used the solution you posted in response as said query. – Sppicy Jan 08 '20 at 10:10
  • Can you give me your query here – whoami - fakeFaceTrueSoul Jan 08 '20 at 11:21
  • ` db.mycollectionname.aggregate([{$unwind: "$Monitor"}, {$group: {_id: "$Monitor.Mon Type", "Total": {sum: 1}}}, {$match: {Total:"Total"}}, {$group: {_id: '', minimum: {$min: "$Total"}, maximum: {$max: "$Total"}, Total: {$sum: "$Total"}, average: {$avg: "$Total"}}}]) ` – Sppicy Jan 08 '20 at 11:22
  • What is that $match between two $groups doing there? After first group Total will be a number you’re filtering documents in match stage with Total = ‘Total’ which leaves no documents for second group stage – whoami - fakeFaceTrueSoul Jan 08 '20 at 11:24
  • oop sorry that was me trying to sort it, here's the query that returns the error; db.Contracts.aggregate([{$unwind: "$Monitor"}, {$group: {_id: "$Monitor.Mon Type", "Total": {sum: 1}}}, {$group: {_id: '', minimum: {$min: "$Total"}, maximum: {$max: "$Total"}, Total: {$sum: "$Total"}, average: {$avg: "$Total"}}}]) – Sppicy Jan 08 '20 at 11:25
  • This should not give you an error it’s working for me what is your mongoDB version? – whoami - fakeFaceTrueSoul Jan 08 '20 at 11:29
  • I'm using mongodb 4.0.14 for shell and 4.0.14 for my cluster on Atlas – Sppicy Jan 08 '20 at 11:32
  • Then this should work can you remove second group stage and see first group stage is returning results as like what you’ve given in question also check minimum maximum and total avg one by one to see where it’s failing – whoami - fakeFaceTrueSoul Jan 08 '20 at 11:35
  • I've tried that and even the initial query I posted returns that error now, going to try my laptop and see if it is just my pc playing up – Sppicy Jan 08 '20 at 11:47
  • @Sppicy : ok issue is in first group stage it has to be $sum what you’ve is just sum change it everything will work – whoami - fakeFaceTrueSoul Jan 08 '20 at 11:53