SQL Tutorial : DRL / DQL - GROUP FUNCTIONS

SQL Tutorial : DRL / DQL - GROUP FUNCTIONS

Let's learn about next topic: GROUP FUNCTIONS

GROUP FUNCTIONS:

*******************

 * DISCARDS NUL VALUES

 * CAN BE NESTED MAXIMUM OF 2 LEVELS

- MULTIPLE I/P'S  RETURNS A SINGLE O/P

SUM

AVG

MAX

MIN

COUNT(*)

COUNT(EXP)
COUNT(DISTINCT EXP)

*SUM AND AVG => NUMERIC COLUMNS

- SELECT SUM(SALARY),SUM(COMMISSION_PCT)

FROM EMPLOYEES

- SELECT COUNT(*) , COUNT(COMMISSION_PCT) ,

COUNT(DISTINCT COMMISSION_PCT)

FROM EMPLOYEES

- SELECT AVG(NVL(COMMISSION_PCT,0))

FROM EMPLOYEES

*MAX AND MIN => ANY DATA TYPES

- SELECT MAX(SALARY),MAX(LAST_NAME),MIN(HIRE_DATE)

FROM EMPLOYEES

- SELECT DEPARTMENT_ID,SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

- SELECT DEPARTMENT_ID,JOB_ID,SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID,JOB_ID

- SELECT DEPARTMENT_ID,JOB_ID,SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID,JOB_ID

HAVING DEPARTMENT_ID =20

* WHERE CLAUSE CANNOT USE GROUP FUNCTIONS

WHERE => FILTERING THE SELECTION

HAVING => FILTERING THE GROUPS

- SELECT D.DEPARTMENT_NAME,SUM(E.SALARY)

FROM EMPLOYEES E,DEPARTMENTS D

WHERE E.DEPARTMENT_ID =D.DEPARTMENT_ID

GROUP BY D.DEPARTMENT_NAME

HAVING D.DEPARTMENT_NAME IN (SELECT DEPARTMENT_NAME

FROM DEPARTMENTS   

WHERE DEPARTMENT_ID IN(30,60))

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID =(SELECT DEPARTMENT_ID

FROM DEPARTMENTS

WHERE LOCATION_ID =(SELECT LOCATION_ID
FROM LOCATIONS
WHERE CITY ='Toronto'))