Aggregate functions and group by clause

Aggregate functions are used for performing group operations.Aggregate functions are used to calculate single values for group of rows. For example, data in the table Emp_company is gicven as follows:

select * from Emp_company;

The output of preceding code snippets is as follows:

ENAME CNAME SALARY JDATE
anil acc 1500 01-may-89
shankar tata 2000 10-jul-90
jaya cmc 1800 07-jun-91
sunil cmc 1700 01-jan-88
vijay tata 5000 03-jan-88
prakash tata 3000 27-may-89
Ajay acc 8000 30-AP-95
Amol acc 1000 17-MA-95

EXAMPLE:

List Maximum salary in all companies

           select max(salary) from Emp_company;

 

output: 

 max(salary)

----------------- 

8000

example2:

maximum salary of employees of acc

 select max(salary) from Emp_company where canme = 'acc';

o/p: 

max(salary)

8000

Example3:  

Displaying the name of employee having maximum salary 

select ename from Emp_company where salary in (select max(salary) from Emp_coomapny);

o/p:   ENAME

          Ajay

The GROUP BY Clause : 

The GROUP BY Clause is used to compute aggregate functions of a group.

example: 

Displaying name of companies and the maximum salary in that company

select cname, max(salary) from Emp_company group by cname;

o/p : 

cname                    max(salary)

acc                         8000

tata                        5000

cmc                       1800

 

example: calculate the number of employees for each company

select cname, count(ENAME) from Emp_company group by cname;

output :

cname                    count(ename)

acc                          3

tata                          3

cmc                          2

The HAVING Clause:

The HAVING Clause is used to providde condition on a group of rows. The where clause is used to provide condition on the row value but the having clausse is used to provide condition on the group value.

Example: Displaying the companies having more than one employee

 select cname, count(ename) from Emp_company group by cname having count(ename)>1;

output:

 

cname                    count(ename)

acc                          3

tata                          3

cmc                          2

THE ORDER BY Clause:

you can sort your data using usiing various ways using the ORDER BY Clause.

ex:  Sorting the Output on ename

select ename, cname from Emp_company order by ename;

ex2: displaying the output in descending order

select ename, cname from Emp_company order by desc;