GROUP FUNCTIONS

GROUP FUNCTIONS

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. 

These functions are: 

 COUNT()

 MAX()

 MIN()

 AVG()

 SUM()

 DISTINCT()

CONDITIONS

1. Group Functions accept one arguments.
2. Group Functions ignore null values.
3. * Used only count functions.
4. Conditions applied only in having Class.

MIN()
To Calculate minimum value of the data 
Ex : 
select min(salary) from employees;

MAX()
To Calculate maximum value of the data 
Ex : 
select max(salary) from employees;

AVG()
To Calculate average value of the data 
Ex : 
select avg(salary) from employees;

COUNT(*)
To Calculate total number of rows in the column 
Ex : 
select count(salary) from employees;
Select count(*) from employees;
Note : * is used only in count operation

SUM()
To Calculate total sum of the value  
Ex : 
select sum(salary) from employees;

It accepts only one arguments

Select Sum(salary,employee_id) from employees;
Select avg(10,6) from dual;

It Ignore null values

Select manager_id from employees;
Select count(manager_id) from employees;

Note : There are 107 records in the manager_id Colomn but count( manager_id ) directs only 106 records because it ignores null values.

GROUP BY

Select department_id, max(salary) from employees;

ORA-00937: not a single-group group function

In group function executes the group of records here department_id is one row so Its make an error. To overcome these type of error group by the department_id column

Select department_id, max(salary) from employees group by department_id ;

Now this query shows the department wise max salary. 

In the Group by Function where clause is replaced by having clause. All the conditions are mentioned by using having clause

Select department_id, max(salary) from employees group by department_id where department_id= 100;

ORA-00933: SQL command not properly ended

Select department_id, max(salary) from employees group by department_id having department_id= 100;


It shows the maximum number of salary