Oracle SQL
03. Oracle Single Row Functions
Eng:- Ahmed Ramadan , Eng:- Marwan Elkordey
Chapter03:Oracle Group Function
Chapter Content :
1. Group Functions
2. Having Clause
Chapter03:Oracle Group Function
Group Functions
What are Group Functions ?
Functions operate on group of rows and return one value for each group
Examples :
Sum - Max - Min - Count - Avg
Chapter03:Oracle Group Function
Group Functions
Group Functions with null Values :
Group functions exclude Null values
Chapter03:Oracle Group Function
Group Functions
Getting number of rows in a table
Count ( * )
Chapter03:Oracle Group Function
Group Functions
Getting Sum of sal for all Employees
Chapter03:Oracle Group Function
Group Functions
Getting avg of comm for all Employees
SELECT AVG(comm)
FROM emp;
SELECT AVG(NVL(comm,0))
FROM emp;
Chapter03:Oracle Group Function
Group Functions
Creating Groups of Data
Select select_list
From tab le_name
Group by group_by_list
Get Sum for each department
Chapter03:Oracle Group Function
Group Functions
Get Sum , max , min ,avg , count
for each department
Chapter03:Oracle Group Function
Group Functions : Grouping By More Than One Column
Get Sum , max , min ,avg , count
for each job in each department
Chapter03:Oracle Group Function
Excluding Group Results :Having Clause
Getting Groups of Employees
Whose Group Sum of salary is > 2500
Chapter03:Oracle Group Function
Using Rollup
Chapter03:Oracle Group Function
Using Cube
Chapter03:Oracle Group Function
Using Grouping Sets
Chapter03:Oracle Group Function
Practices
1. Write a query to display the sum of salary for each department
2. Write a query to display the sum for each job in each department
3. Write a query to display the min sal in each department
4. Write a query to display the min paid employee in each job
5. Write a query to display the min sal in each job and the min salary in emp table
6. Write a query to display the sum , max , min ,avg of salary in each job for department no 30
7. Write a query to display the count of employees in each job
8. Write a query to display the count of employees in emp table
9. Write a query to display the count of employees in each department and the count of employees in emp table
10. Write a query to display the max salary in each department and the max salary in each job
Chapter03:Oracle Group Function
Practices
1. Write a query to display the sum of salary for each department
select deptno , sum(sal) from emp group by deptno
2. Write a query to display the sum for each job in each department
select deptno , job , sum(sal) from emp group by deptno , job
3. Write a query to display the min sal in each department
select deptno , min(sal) from emp group by deptno;
4. Write a query to display the min paid employee in each job
select min (sal) from emp group by job ;
5. Write a query to display the min sal in each job and the min salary in emp table
select job , min ( sal ) from emp group by rollup (job) ;
6. Write a query to display the sum , max , min ,avg of salary in each job for department no 30
select job , max ( sal ) , min (sal) , sum(sal) , avg(sal) from emp where deptno = 30 group by job ;
7. Write a query to display the count of employees in each job
select job , count ( * ) from emp group by job
8. Write a query to display the count of employees in emp table
select count ( * ) from emp ;
9. Write a query to display the count of employees in each department and the count of employees in emp table
select deptno , count (*) from emp group by rollup ( deptno)
10. Write a query to display the max salary in each department and the max salary in each job
select deptno , job , max(sal) from emp group by grouping sets ( ( deptno) , ( job ) )
