Aggregate Functions (Group Functions) , HAVING clause in SQL

 In this lesson ,

We need to get valuable data by join multiple tables 

Using Oracle Free SQL  , by select HR schema to connect to all HR tables 

in past lesson we get data using a query like 

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

ORDER BY 2 

which list data of employee and related department name 

Let's we need to get how many employees in each department 

we can write query like this 👇

SELECT COUNT(EMPLOYEE_ID) "Count of Employees" ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

group by DEPARTMENT_NAME

ORDER BY 2

Explanation of Query :

we get "how many employees" using COUNT function , for more information about COUNT , you can see this page

now , let we want to know MAXIMUM salary in each department , we will write this 👇 query

SELECT MAX(SALARY) "Max Salary per Department" ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

group by DEPARTMENT_NAME

ORDER BY 2

by same concept , we can get MINIMUM, AVERAGE , TOTAL (SUMMATION) of salary per each department

you can use this by yourself using MIN(SALARY) , AVG(SALARY) , SUM(SALARY) , it is amazing to get valuable information when know more in SQL

for more information about aggregate function , you can see this page

as we use aggregate function , we must talk about HAVING clause .

firstly what is HAVING clause ? simply , this clause filters /restricts data by aggregate function ,

or we can say ,The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list

for example , let's try to get department name which only has max salary less than 10000 using this 👇query

SELECT MAX(SALARY) "Max Salary per Department" ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

group by DEPARTMENT_NAME

having MAX(SALARY) < 10000

ORDER BY 2

as you see here , only 4 rows are selected , which match the condition MAX(SALARY) < 10000

hint1 : condition in HAVING clause must use the same used aggregate function in such query , as HAVE will filter data coming by execute the query itself

hint2 : HAVING clause must be used with GROUP BY clause

in this lesson , we take a look at Aggregate Function , HAVING clause in oracle SQL ,

and in next lesson we will use Subqueries to get more valuable information.