Hi guy’s , here the query to achieve group by clause in mongodb. we must $group function to make columns into groups . lets see the following queries.
- To Display All Records of Tables.
SCOTT.EMP Table
db.getCollection("SCOTT.EMP").find({});
2. To Display Sum of salary of table
SCOTT.EMP Table name , SUM aggregate function. SAL column name.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
},
"SUM(SAL)" : {
"$sum" : "$SAL"
}
}
},
{
"$project" : {
"SUM(SAL)" : "$SUM(SAL)",
"_id" : NumberInt(0)
}
}
]
);
3. To Display Maximum salary on the table
SCOTT.EMP Table name , MAX aggregate function,SAL column name.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
},
"MAX(SAL)" : {
"$max" : "$SAL"
}
}
},
{
"$project" : {
"MAX(SAL)" : "$MAX(SAL)",
"_id" : NumberInt(0)
}
}
]
);
4. To Display Sum of salary of table Department Wise
SCOTT.EMP Table name , SUM aggregate function. DEPTNO , SAL columns.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
"DEPTNO" : "$DEPTNO"
},
"SUM(SAL)" : {
"$sum" : "$SAL"
}
}
},
{
"$project" : {
"DEPTNO" : "$_id.DEPTNO",
"SUM(SAL)" : "$SUM(SAL)",
"_id" : NumberInt(0)
}
}
]
);
5. To Display Sum of salary of table grouping with DEPTNO,EMPNO
SCOTT.EMP Table name , SUM aggregate function. DEPTNO,EMPNO,SAL columns.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
"DEPTNO" : "$DEPTNO",
"EMPNO" : "$EMPNO"
},
"SUM(SAL)" : {
"$sum" : "$SAL"
}
}
},
{
"$project" : {
"EMPNO" : "$_id.EMPNO",
"DEPTNO" : "$_id.DEPTNO",
"SUM(SAL)" : "$SUM(SAL)",
"_id" : NumberInt(0)
}
}
]
);
6. To Display Sum of salary of table
SCOTT.EMP Table name , SUM aggregate function.
Condition DEPTNO = 10 using match function. grouping deptno aggregate function on SAL column.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$match" : {
"DEPTNO" : NumberLong(10)
}
},
{
"$group" : {
"_id" : {
"DEPTNO" : "$DEPTNO"
},
"SUM(SAL)" : {
"$sum" : "$SAL"
}
}
},
{
"$project" : {
"DEPTNO" : "$_id.DEPTNO",
"SUM(SAL)" : "$SUM(SAL)",
"_id" : NumberInt(0)
}
}
]
);
7. To Display Sum of salary of table
SCOTT.EMP Table name , SUM aggregate function.
Condition DEPTNO = 20 using match function in last. grouping deptno aggregate function on SAL column.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
"DEPTNO" : "$DEPTNO"
},
"SUM(SAL)" : {
"$sum" : "$SAL"
}
}
},
{
"$project" : {
"DEPTNO" : "$_id.DEPTNO",
"SUM(SAL)" : "$SUM(SAL)",
"_id" : NumberInt(0)
}
},
{
"$match" : {
"DEPTNO" : NumberLong(20)
}
}
]
);
8. To Display Sum of salary of table
SCOTT.EMP Table name , SUM aggregate function.
Condition SUM(SAL) > 1000 using match function. grouping deptno aggregate function on SAL column count function applied on deptno.
db.getCollection("SCOTT.EMP").aggregate(
[
{
"$group" : {
"_id" : {
"DEPTNO" : "$DEPTNO"
},
"SUM(SAL)" : {
"$sum" : "$SAL"
},
"COUNT(DEPTNO)" : {
"$sum" : NumberInt(1)
}
}
},
{
"$project" : {
"DEPTNO" : "$_id.DEPTNO",
"SUM(SAL)" : "$SUM(SAL)",
"COUNT(DEPTNO)" : "$COUNT(DEPTNO)",
"_id" : NumberInt(0)
}
},
{
"$match" : {
"SUM(SAL)" : {
"$gt" : NumberLong(1000)
}
}
}
]
);
