Grouping data - Oracle SQL quick ref 1

Oracle SQL group functions operate on sets of rows to give one result per group of inputs(In other words, many inputs, one output): AVG,COUNT,MAX,MIN,SUM...

SELECT MAX(salary) max, MIN(salary) min, SUM(salary) sum, ROUND(AVG(salary),0) avg
FROM employees;

SELECT COUNT(*) numberOfEmployees
FROM employees;

Sometimes the rows of data, may contain duplications that we don't want to be taken into account.
Then the DISTINCT keyword, can be useful to suppress the duplicates.

SELECT COUNT(DISTINCT department_id)
FROM employees

By default group functions will ignore null values but if we want to not ignore then, we should use the NVL function.

SELECT NVL(AVG(commission_pct))
FROM employees;

It is possible in sql to select different groups/sub sets of data, from one same table, this is know as grouping. The GROUP BY keyword allows us to divide the rows of a table into groups and optionally latter apply a group function to act upon each of those groups.
For example, we could get the average salary for each department using the group by function:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; 

Important!: If a SELECT statement contains a GROUP BY clause, all non aggregate functions(department_id) that are defined in the SELECT, need to also be in the GROUP BY clause.

When working with GROUP BY and we want to add additional restrictions to the groups, we are not allowed to use the WHERE clause, instead we must use the HAVING clause.
For example lets say that we want to see the departments and their max salaries, but only if the max salary of the department is greater than 10000:

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;