Sum ( ) , it is a function which return the aggregate sum of any expression , like salary of multiple employee , sum of population in any particular area etc .
Syntax :
- select sum ( Expression ) from student where clause ; // expression can be numeric value or any mathematical formula
select sum ( Expression ) from student where clause ; // expression can be numeric value or any mathematical formula
Example :
Table name : Employee_Info
- Employee_name Employee_Age Employee _Salary
- Mukesh 23 100000
- Ayush 24 200000
- Ishan 20 400000
- Pranav 35 700000
- Abhishek 26 800000
- Ravi 25 300000
- David 40 800000
Employee_name Employee_Age Employee _Salary
Mukesh 23 100000
Ayush 24 200000
Ishan 20 400000
Pranav 35 700000
Abhishek 26 800000
Ravi 25 300000
David 40 800000
Suppose you want to retrieve Sum of Employee Salary whose salary is greater than 400000 .
- select sum ( Employee_Salary ) as " Total Salary " from Employee_Info where Employee_Salary > 400000
select sum ( Employee_Salary ) as " Total Salary " from Employee_Info where Employee_Salary > 400000
Output :
- Employee_name Employee_Age Employee _Salary
- Pranav 35 700000
- Abhishek 26 800000
- David 40 800000
Employee_name Employee_Age Employee _Salary
Pranav 35 700000
Abhishek 26 800000
David 40 800000
In this result if two employee have same salary then both will come in Output . As you can see above example
If you want to select distinct employee then you can also do , How ? see below :
- select sum ( distinct Employee_Salary ) as " total salary " from Employee_Info where Employee_Salary > 400000
select sum ( distinct Employee_Salary ) as " total salary " from Employee_Info where Employee_Salary > 400000
Output :
- Employee_name Employee_Age Employee _Salary
- Pranav 35 700000
Employee_name Employee_Age Employee _Salary
Pranav 35 700000
In above result you can see , it return only distinct salary .
Sometime we have to use Group By statement with Sum function because sometime we have a table in which two or more department are same and we have to group them and then calculate the result . For example see below :
Table name -> Branch_Department
- Department Sales
- Mechanical 360
- Electrical 100
- Electrical 110
- Electronics 150
- Electronics 170
Department Sales
Mechanical 360
Electrical 100
Electrical 110
Electronics 150
Electronics 170
In above example you want to sum of different department then you can do this by Group By statment :
- select Department , sum ( Sales ) as " total sales " from Branch_Department Group By Department ;
select Department , sum ( Sales ) as " total sales " from Branch_Department Group By Department ;
Output :
- Department Sales
- Mechanical 360
- Electrical 210
- electronics 320
Department Sales
Mechanical 360
Electrical 210
electronics 320
0 Comment(s)