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:

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:

Contact me

Name

Email
*

Message
*

About javing

I want to welcome you to my blog.My name is Djordje Popovic, I am a Software Craftsman and an enthusiast of the java programming language.

In 2011 I decided to create javing just as a way to keep track of my studies on the java programming language and also provide support to other fellow students. This blog has being here since the beginning of my career as a programmer. Fell free to make comments or critics to my entries, ill be happy to read them and answer to you.