GROUP FUNCTIONS

Query:

SQL> SELECT AVG(comm)

FROM EMP;

 Output:

AVG(COMM)

———

550

Query:

 

SQL> SELECT AVG(NVL(comm,0))

FROM EMP;

 

 

Output:

 

AVG(NVL(COMM,0))

—————-

157.14286

 

 

GROUP BY:

It is used to create groups for an application of group functions.

 

 

Query:

 

SQL> SELECT deptno, AVG(sal)

FROM EMP

GROUP BY deptno;

 

 

Output:

 

DEPTNO   AVG(SAL)

———    ———

10 2916.6667

20      2175

30 1566.6667

 

 

The ‘GROUP BY’ column doesn’t have to be in the select list. Multiple columns can be used to group the results.

 

 

Query:

 

SQL> SELECT deptno, job, sum(sal)

FROM EMP

GROUP BY deptno, job;

 

Output:

 

DEPTNO     JOB          SUM(SAL)

——— ———    ———

10        CLERK               1300

10        MANAGER        2450

 

 

ILLEGAL QUERIES:

 

 

Query:

 

SQL> SELECT deptno, count(ename)

FROM EMP;

 

Output:

 

ERROR at line 1:

ORA-00937: not a single-group group function

 

This is illegal because all fields which are not group functions must be included in the group by clause.

 

Important!!

When using group functions in conditions, use the HAVING clause because WHERE cannot be used.

 

 

Query:

 

SQL> SELECT deptno, AVG(sal)

FROM EMP

GROUP BY deptno

HAVING AVG(sal)>2000;

 

Output:

 

DEPTNO            AVG(SAL)

———        ———

10       2916.6667

20            2175

 

 

Query:

 

SQL> SELECT deptno, MAX(sal)

FROM EMP

GROUP BY deptno

HAVING MAX(sal)>2900;

 

 

Output:

 

DEPTNO            MAX(SAL)

———        ———

10             5000

20             3000

 

 

 

 

SE1> Display the job title & total monthly salary for each job title with the total payroll exceeding $5000 & don’t include jobs related to sales.

 

 

 

NESTING:

 

Query:

 

SQL> SELECT MAX(AVG(sal))

FROM EMP

GROUP BY deptno;

 

Output:

 

MAX(AVG(SAL))

————-

2916.6667

 

 SE2) Find the maximum salary from emp table:

 

 

SE3) Find the maximum salary for each job:

 

SE4) Display all jobs having maximum salary greater than 2000:

 

SE5) Print the number of employees in each job:

 

 

SE6) Display the total number of managers:

 

SE7) Produce the output:

DNAME      LOC       NO. OF PEOPLE   SALARY

———–          ——          ——————— ————

ACCOUNTING NEWYORK         3          2916.67

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s