Course Module 4. Working with databases - Lecture: Advanced grouping

5.1 HAVING statement

There is another useful operator in SQL that is used with GROUP BY, it's called HAVING.

In its meaning, it is completely analogous to the operator WHERE. It only WHEREallows you to set a row filter before grouping, and with the help HAVINGyou can set a filter that is applied to records after grouping.

The general view of the query when using grouping and filtering grouping results is as follows:

SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition

HAVINGcan only be used if the request contains GROUP BY.

Let's write a query where we display the number of hired employees by year.

SELECT
YEAR(join_date) AS hire_year,
COUNT(*) AS total
FROM employee
GROUP BY hire_year

And the result of this query:

hire_year	total
2012	1
2013	1
2014	1
2015	2
2018	1

And now we exclude from it the years when one or fewer employees were hired. Example:

SELECT
YEAR(join_date) AS hire_year,
COUNT(*) AS total
FROM employee
GROUP BY hire_year
HAVING total > 1

And the result of this query:

hire_year	total
2015	2

5.3 Order of execution of statements

To write correct and efficient SQL queries, you need to understand how they are executed by the SQL server.

The procedure for performing actions is strictly regulated and does not depend on your desire. You cannot rearrange the operators and get a different order.

The SQL query is executed in several stages in this order.

Stage 1 - fetching rows

First, all rows from the specified table are selected.
Then calculated fields are added to them.
And then of all the rows, only those that satisfy the condition remainWHERE

Stage 2 - grouping

Grouping is then applied to the results.
During grouping, fields such as COUNT(*).
Finally, a filter is applied to the grouping result HAVING.

Stage 3 - sorting

The rows obtained in the previous steps are sorted using ORDER BY.

Finally, the result can be trimmed with LIMITand OFFSET.