Group Functions
NULL value in group functions
Group function ignore null values while calculating the result
Count()
Helps us to count total number of non null records
|
Roll |
Name |
Age |
|
101 |
Abc |
10 |
|
102 |
Xyz |
12 |
|
101 |
Abc |
10 |
|
101 |
Zzz |
14 |
|
104 |
|
15 |
|
105 |
Abc |
12 |
|
102 |
Ttt |
17 |
Select * from std;
All the records will be displayed
Select count(*) from std;
7
Select count(roll) from std;
7
select count(name) from std;
6
Select count(distinct name ) from std;
4
select count(distinct roll) from std;
4
select count(distinct *) from std;
6
Select count(*), count(name),count(distinct name) from std;
7 6 4
Q. List of designations/jobs/post in the company.
Select distinct(desig) from company;
Q. List of departments in the company.
Sol:
Select distinct(deptname) from company;
Q.Total number of designation/job/post in the company?
Sol:
Select count(distinct design) from company;
Q. Total departments in the company.
Sol:
Select count(distinct deptname) from company;
Q. total number of managers in the company.
Sol:
Select count(*) from emp where design=’manager’;
Q. total number of employees working in production department.
Sol:
Select count(*) from emp where deptname=’production’;
Q. total number of employees getting salary in 5 digits.
Sol:
Select count(*) from emp where sal between 10000 and 99999;
Sum()
Helps us to calculate sum of all the values of a specified column.
Q. Total salary distributed in the company.
Sol:
Select sum(sal) from emp;
Q. total salary distributed to managers of the company.
Sol:
Select sum(sal) from emp where desig=’manager’;
Q. total salary distributed in production department.
Sol:
Select sum(sal) from emp where deptname=’production’;
//error
Select empno,sum(sal) from emp;
Avg()
Helps us to calculate avg of all the values of a specified column
Q. average salary distributed in the company.
Sol:
Select avg(sal) from emp;
Q. average salary distributed to managers of the company.
Sol:
Select avg(sal) from emp where desig=’manager’;
Q. average salary distributed in production department.
Sol:
Select avg(sal) from emp where deptname=’production’;
Max()
Helps us to find the maximum value of the specified column.
Q. maximum salary given to any employee.
Sol:
Select max(sal) from emp;
Q. maximum salary given to to any manager.
Sol:
Select max(sal) from emp where design=’manager’;
Q. maximum salary give in production department.
Sol:
Select max(sal) from emp where deptname=’production’;
Min()
Helps us to find the minimum value of the specified column.
Q. Minimum salary given to any employee.
Sol:
Select min(sal) from emp;
Q. minimum salary given to to any manager.
Sol:
Select min(sal) from emp where desig=’manager’;
Greatest()
Helps us to find the greatest value of given values / specified list of columns
Select greatest(10,20,30) from dual;
Sol:
30
Select greatest(m1,m2,m3) from student;
One value for each record will be displayed.
Least()
Helps us to find the least value of given values / specified list of columns
Select least(10,20,30) from dual;
10
Select least(m1,m2,m3) from student;
One output for each record will be displayed.
