Here we will explore group by clause in mongo.
Inserting Sample Data:
Query:
db.studentinfo.insertMany([
{"name":"sagar","class":10,"marathi":80,"english":75,"history":68,"geography":78,"hindi":67,"math":76,"science1":67,"science2":70,"region":"rural","school":"A"},
{"name":"sangram","class":10,"marathi":81,"english":72,"history":60,"geography":72,"hindi":62,"math":70,"science1":68,"science2":72,"region":"rural","school":"A"},
{"name":"sachin","class":10,"marathi":69,"english":62,"history":80,"geography":82,"hindi":72,"math":70,"science1":78,"science2":70,"region":"urban","school":"B"},
{"name":"swapnil","class":10,"marathi":60,"english":60,"history":80,"geography":72,"hindi":82,"math":60,"science1":68,"science2":80,"region":"urban","school":"C" },
{"name":"rock","class":10,"marathi":81,"english":72,"history":70,"geography":72,"hindi":62,"math":74,"science1":68,"science2":69,"region":"rural","school":"A"},
{"name":"mary","class":10,"marathi":67,"english":71,"history":80,"geography":82,"hindi":72,"math":69,"science1":78,"science2":84,"region":"urban","school":"B"},
]);
output:
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5aa4b8fae7232cc5677a7738"),
ObjectId("5aa4b8fae7232cc5677a7739"),
ObjectId("5aa4b8fae7232cc5677a773a"),
ObjectId("5aa4b8fae7232cc5677a773b")
]
}
Sum:
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: "$region", total: { $sum: "$marathi" } } },
])
Group by Multiple Columns:
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: "$marathi" } } },
])
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
])
db.studentinfo.aggregate([
{ $match: { class: 10,region:"rural" } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
])
Projection:
db.studentinfo.aggregate([
{ $match: { class: 10,region:"rural" } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
])
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
])
Sorting:
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
{$sort:{"region":1,"school":1}}
])
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $avg: "$marathi" }, hindi: { $avg: "$hindi" } , english: { $avg: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
{$sort:{"region":1,"school":1}}
])
Making Sum of two column in mongo
Query:
db.studentinfo.aggregate([
{ "$project" : {
'name':'$name',
'socialscience' : { '$add' : [ '$history','$geography' ] },
'science' : { '$add' : [ '$science1','$science2' ] },
'language' : { '$add' : [ '$marathi', '$hindi','$english' ] },
}
}
])
Output:
{ "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "socialscience" : 146, "science" : 137, "language" : 222 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "socialscience" : 132, "science" : 140, "language" : 215 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "socialscience" : 162, "science" : 148, "language" : 203 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "socialscience" : 152, "science" : 148, "language" : 202 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "socialscience" : 142, "science" : 137, "language" : 215 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "socialscience" : 162, "science" : 162, "language" : 210 }
Query:
db.studentinfo.aggregate([
{ "$project" : {
'name':'$name',
'class':'$class',
'region':'$region',
'school':'school',
'total_socialscience' : { '$add' : [ '$history','$geography' ] },
'total_science' : { '$add' : [ '$science1','$science2' ] },
'total_language' : { '$add' : [ '$marathi', '$hindi','$english' ] },
'avg_science':{ $divide: [ { '$add' : [ '$science1','$science2' ] }, 2 ] },
'avg_socialscience':{ $divide: [ { '$add' : [ '$history','$geography' ] }, 2 ] },
'avg_language':{ $divide: [ { '$add' : [ '$marathi', '$hindi','$english' ] }, 3 ] },
}
}
])
Output:
{ "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 146, "total_science" : 137,
"total_language" : 222, "avg_science" : 68.5, "avg_socialscience" : 73, "avg_language" : 74 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 132, "total_science" : 140,
"total_language" : 215, "avg_science" : 70, "avg_socialscience" : 66, "avg_language" : 71.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 148,
"total_language" : 203, "avg_science" : 74, "avg_socialscience" : 81, "avg_language" : 67.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 152, "total_science" : 148,
"total_language" : 202, "avg_science" : 74, "avg_socialscience" : 76, "avg_language" : 67.33333333333333 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 142, "total_science" : 137,
"total_language" : 215, "avg_science" : 68.5, "avg_socialscience" : 71, "avg_language" : 71.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 162,
"total_language" : 210, "avg_science" : 81, "avg_socialscience" : 81, "avg_language" : 70 }
Student Appeared for exam
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: 1 } } },
])
output:
{ "_id" : { "region" : "urban", "class" : 10 }, "total" : 3 }
{ "_id" : { "region" : "rural", "class" : 10 }, "total" : 3 }
Does student 80 or higher mark in marathi
db.studentinfo.aggregate(
{ $project: {
_id: 0,
name: 1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 80]},
1,
0
]}
}},
{ $group: {
_id: "$name",
marathiFi: {$sum: '$marathiFilterd'}
}});
output:
{ "_id" : "sagar", "marathiFi" : 0 }
{ "_id" : "sangram", "marathiFi" : 1 }
{ "_id" : "sachin", "marathiFi" : 0 }
{ "_id" : "mary", "marathiFi" : 0 }
{ "_id" : "rock", "marathiFi" : 1 }
{ "_id" : "swapnil", "marathiFi" : 0 }
Student with First class in marathi & english:
db.studentinfo.aggregate(
{ $project: {
_id: 0,
class: 1,
region:1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 60]},
1,
0
]},
englishFilterd: {$cond: [
{$gt: ['$english', 60]},
1,
0
]}
}},
{ $group: {
_id: {class:"$class",region:"$region"},
marathiFi: {$sum: '$marathiFilterd'},
englishFi: {$sum: '$englishFilterd'},
total:{$sum:1}
}});
output:
{ "_id" : { "class" : 10, "region" : "urban" }, "marathiFi" : 2, "englishFi" : 2, "total" : 3 }
{ "_id" : { "class" : 10, "region" : "rural" }, "marathiFi" : 3, "englishFi" : 3, "total" : 3 }
Project multiple times:
db.studentinfo.aggregate(
{ $project: {
_id: 0,
class: 1,
region:1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 60]},
1,
0
]},
englishFilterd: {$cond: [
{$gt: ['$english', 60]},
1,
0
]}
}},
{ $group: {
_id: {class:"$class",region:"$region"},
marathiFi: {$sum: '$marathiFilterd'},
englishFi: {$sum: '$englishFilterd'},
total:{$sum:1}
}},
{ $project: {class : '$_id.class',region:'$_id.region',total:'$total',marathiFirstclass:'$marathiFi',englishFirstclass:'$englishFi',_id:0}}
);
Inserting Sample Data:
Query:
db.studentinfo.insertMany([
{"name":"sagar","class":10,"marathi":80,"english":75,"history":68,"geography":78,"hindi":67,"math":76,"science1":67,"science2":70,"region":"rural","school":"A"},
{"name":"sangram","class":10,"marathi":81,"english":72,"history":60,"geography":72,"hindi":62,"math":70,"science1":68,"science2":72,"region":"rural","school":"A"},
{"name":"sachin","class":10,"marathi":69,"english":62,"history":80,"geography":82,"hindi":72,"math":70,"science1":78,"science2":70,"region":"urban","school":"B"},
{"name":"swapnil","class":10,"marathi":60,"english":60,"history":80,"geography":72,"hindi":82,"math":60,"science1":68,"science2":80,"region":"urban","school":"C" },
{"name":"rock","class":10,"marathi":81,"english":72,"history":70,"geography":72,"hindi":62,"math":74,"science1":68,"science2":69,"region":"rural","school":"A"},
{"name":"mary","class":10,"marathi":67,"english":71,"history":80,"geography":82,"hindi":72,"math":69,"science1":78,"science2":84,"region":"urban","school":"B"},
]);
output:
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5aa4b8fae7232cc5677a7738"),
ObjectId("5aa4b8fae7232cc5677a7739"),
ObjectId("5aa4b8fae7232cc5677a773a"),
ObjectId("5aa4b8fae7232cc5677a773b")
]
}
Sum:
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: "$region", total: { $sum: "$marathi" } } },
])
Group by Multiple Columns:
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: "$marathi" } } },
])
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
])
db.studentinfo.aggregate([
{ $match: { class: 10,region:"rural" } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
])
Projection:
db.studentinfo.aggregate([
{ $match: { class: 10,region:"rural" } },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
])
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
])
Sorting:
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
{$sort:{"region":1,"school":1}}
])
db.studentinfo.aggregate([
{ $match: { class: 10} },
{ $group: { _id: {"region": "$region","school": "$school"}, marathi: { $avg: "$marathi" }, hindi: { $avg: "$hindi" } , english: { $avg: "$english" }} },
{$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
{$sort:{"region":1,"school":1}}
])
Making Sum of two column in mongo
Query:
db.studentinfo.aggregate([
{ "$project" : {
'name':'$name',
'socialscience' : { '$add' : [ '$history','$geography' ] },
'science' : { '$add' : [ '$science1','$science2' ] },
'language' : { '$add' : [ '$marathi', '$hindi','$english' ] },
}
}
])
Output:
{ "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "socialscience" : 146, "science" : 137, "language" : 222 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "socialscience" : 132, "science" : 140, "language" : 215 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "socialscience" : 162, "science" : 148, "language" : 203 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "socialscience" : 152, "science" : 148, "language" : 202 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "socialscience" : 142, "science" : 137, "language" : 215 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "socialscience" : 162, "science" : 162, "language" : 210 }
Query:
db.studentinfo.aggregate([
{ "$project" : {
'name':'$name',
'class':'$class',
'region':'$region',
'school':'school',
'total_socialscience' : { '$add' : [ '$history','$geography' ] },
'total_science' : { '$add' : [ '$science1','$science2' ] },
'total_language' : { '$add' : [ '$marathi', '$hindi','$english' ] },
'avg_science':{ $divide: [ { '$add' : [ '$science1','$science2' ] }, 2 ] },
'avg_socialscience':{ $divide: [ { '$add' : [ '$history','$geography' ] }, 2 ] },
'avg_language':{ $divide: [ { '$add' : [ '$marathi', '$hindi','$english' ] }, 3 ] },
}
}
])
Output:
{ "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 146, "total_science" : 137,
"total_language" : 222, "avg_science" : 68.5, "avg_socialscience" : 73, "avg_language" : 74 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 132, "total_science" : 140,
"total_language" : 215, "avg_science" : 70, "avg_socialscience" : 66, "avg_language" : 71.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 148,
"total_language" : 203, "avg_science" : 74, "avg_socialscience" : 81, "avg_language" : 67.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 152, "total_science" : 148,
"total_language" : 202, "avg_science" : 74, "avg_socialscience" : 76, "avg_language" : 67.33333333333333 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 142, "total_science" : 137,
"total_language" : 215, "avg_science" : 68.5, "avg_socialscience" : 71, "avg_language" : 71.66666666666667 }
{ "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 162,
"total_language" : 210, "avg_science" : 81, "avg_socialscience" : 81, "avg_language" : 70 }
Student Appeared for exam
db.studentinfo.aggregate([
{ $match: { class: 10 } },
{ $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: 1 } } },
])
output:
{ "_id" : { "region" : "urban", "class" : 10 }, "total" : 3 }
{ "_id" : { "region" : "rural", "class" : 10 }, "total" : 3 }
Does student 80 or higher mark in marathi
db.studentinfo.aggregate(
{ $project: {
_id: 0,
name: 1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 80]},
1,
0
]}
}},
{ $group: {
_id: "$name",
marathiFi: {$sum: '$marathiFilterd'}
}});
output:
{ "_id" : "sagar", "marathiFi" : 0 }
{ "_id" : "sangram", "marathiFi" : 1 }
{ "_id" : "sachin", "marathiFi" : 0 }
{ "_id" : "mary", "marathiFi" : 0 }
{ "_id" : "rock", "marathiFi" : 1 }
{ "_id" : "swapnil", "marathiFi" : 0 }
Student with First class in marathi & english:
db.studentinfo.aggregate(
{ $project: {
_id: 0,
class: 1,
region:1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 60]},
1,
0
]},
englishFilterd: {$cond: [
{$gt: ['$english', 60]},
1,
0
]}
}},
{ $group: {
_id: {class:"$class",region:"$region"},
marathiFi: {$sum: '$marathiFilterd'},
englishFi: {$sum: '$englishFilterd'},
total:{$sum:1}
}});
output:
{ "_id" : { "class" : 10, "region" : "urban" }, "marathiFi" : 2, "englishFi" : 2, "total" : 3 }
{ "_id" : { "class" : 10, "region" : "rural" }, "marathiFi" : 3, "englishFi" : 3, "total" : 3 }
Project multiple times:
db.studentinfo.aggregate(
{ $project: {
_id: 0,
class: 1,
region:1,
marathiFilterd: {$cond: [
{$gt: ['$marathi', 60]},
1,
0
]},
englishFilterd: {$cond: [
{$gt: ['$english', 60]},
1,
0
]}
}},
{ $group: {
_id: {class:"$class",region:"$region"},
marathiFi: {$sum: '$marathiFilterd'},
englishFi: {$sum: '$englishFilterd'},
total:{$sum:1}
}},
{ $project: {class : '$_id.class',region:'$_id.region',total:'$total',marathiFirstclass:'$marathiFi',englishFirstclass:'$englishFi',_id:0}}
);
No comments:
Post a Comment