Condition after executing a sql query having. HAVING SQL: description, syntax, examples

Finally, the last section used to evaluate a table expression is HAVING(if present).

Chapter HAVING can only appear meaningfully in a table expression if it contains a section GROUP BY. The search condition of this section specifies a condition on a group of rows in a grouped table. Formally section HAVING may also be present in a table expression that does not contain GROUP BY. In this case, the result of the calculation of the previous partitions is assumed to be a grouped table consisting of a single group with no dedicated grouping columns.

Section search condition HAVING is built according to the same syntactic rules as the section search condition WHERE and may include the same predicates. However, there are special syntactic restrictions regarding the use in the search condition of the table column specifications from the section FROM given table expression. These restrictions follow from the fact that the section search condition HAVING sets the condition on the whole group, and not on individual rows.

Therefore, in the arithmetic expressions of the predicates included in the selection condition of the section HAVING, you can directly use only the specifications of the columns specified as grouping columns in the section GROUP BY. The remaining columns can only be specified within aggregate function specifications COUNT, SUM, AVG, MIN And MAX, which in this case calculate some aggregate value for the entire group of rows. The situation is similar with subqueries included in the predicates of the section’s selection condition HAVING: If the current group characteristic is used in the subquery, it can only be specified by reference to the grouping columns.

The result of the section execution HAVING is a grouped table containing only those groups of rows for which the result of calculating the search condition is TRUE. In particular, if the section HAVING present in a table expression that does not contain GROUP BY, then the result of its execution will be either an empty table or the result of executing the previous sections of the table expression, treated as one group without grouping columns.

HAVING COUNT

Select codes for products purchased by more than one buyer:

SELECT stock FROM ordsale GROUP BY stock HAVING COUNT(*) > 1;

HAVING MIN

Get the minimum and maximum salaries for clerks in each department where the lowest salary is less than $1,000:

SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal)

Example 2. The database and table are the same as in example 1.

Using the SQL HAVING and GROUP BY operators, you need to display categories in any part of which the minimum number of submitted advertisements does not exceed 100. To do this, write the following query:

SELECT Category, Units, MIN (Units) AS Minimum FROM Ads GROUP BY Category HAVING MIN (Units)

Example 3. The database and table are the same as in the previous examples.

Using the SQL HAVING and GROUP BY statements, you want to display ad categories that have more than two parts. We write the following request:

SELECT Category, Part FROM Ads GROUP BY Category HAVING COUNT (*)>2

The result will be the following table:

Category	Part
Leisure	Books
Leisure	Music
Leisure	Games
Real estate	Apartments
Real estate	Dachas
Real estate	At home

Work independently with the Staff table of the company database. It contains columns Name (last name), Dept (department number), Years (length of work experience) and Salary (salary amount). Examples for independent solutions with links to check the solution are after the table.

Name	Dept	Years	Salary
Sanders	20	7	18357.5
Junkers	15	6	16232.8
Moonlight	15	8	21500.6
Pernal	20	8	18171.2
Aisen	15	7	19540.7
McGregor	15	7	15790.8
Marenghi	38	5	17506.8
Doctor	20	5	12322.4
factor	38	8	16228.7

Example 5. Determine the numbers of departments in which the average length of service of employees is more than 6.5 years.

SQL HAVING statement and comparison with the value returned by the ALL or ANY (SOME) quantifier

The SQL HAVING statement can be used to retrieve data that matches the results of a comparison not only with a given number, but also with the value returned by the ALL or ANY (SOME) quantifier. The ALL quantifier returns the maximum value from the query to which it is applied, and then, using the HAVING operator, a comparison with the maximum value is performed. For example, ALL(10, 15, 20) will return 20. The ANY quantifier (and its analogue SOME) returns the minimum value and then using the HAVING operator a comparison with the minimum value is performed. The syntax for a query with the SQL HAVING statement, which specifies a comparison with the value returned by the ALL or ANY (SOME) quantifier, is as follows:.

SELECT COLUMN_NAMES FROM TABLE_NAME GROUP BY COLUMN_NAME HAVING AGREGATE_FUNCTION(COLUMN NAME) COMPARISON_OPERATOR QUANTITOR ( SELECT AGREGATE_FUNCTION(COLUMN NAME) FROM TABLE_NAME GROUP BY COLUMN_NAME)

Example 7. There is a database "Theater". It has a Play table containing data about productions in the theater. This table contains the fields PlayID (identifier), Name (title), Genre (genre), Author (author), Dir_ID (foreign key - director identifier), PremiereDate (premiere date), LastDate (end date). It is required to determine the most popular theater genre, that is, the genre in which the largest number of productions have been staged.

Using the SQL HAVING and GROUP BY statements, we write the first part of the query to the Play table, which compares the number of rows grouped by genre:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT (*) >=

Now you need to determine what to compare with. This is the maximum number of records in the same table, grouped by genre. Therefore, we need the quantifier ALL. We write the second part of the request:

ALL (SELECT COUNT (*) FROM PLAY GROUP BY Genre)

The entire query to determine the most popular genre in the theater would be as follows:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT (*) >= ALL (SELECT COUNT (*) FROM PLAY GROUP BY Genre)

SQL HAVING statement in table joins

Let's return to queries with the SQL HAVING statement, in which the comparison is carried out with a given number, as in the first paragraph. But let's complicate the task. In practice, often the number of rows in a query result is determined by a foreign key referencing another table.

Example 8. We continue to work with the Theater database. We will need tables Play, containing data about productions in the theater, and Team, containing data about the roles of actors. You want to display a list of one-man performances (performances with one actor). Below is a diagram of the "Theater" database (to enlarge the picture, click on it with the left mouse button).

To be even more precise, we need to choose performances in which there is only one role. Among the fields in the Team table is PlayID, a foreign key that references the Play table. In each Team table entry, this foreign key identifies the production in which the role is played. If we join the Play and Team tables using the PlayID key, we can determine the number of roles in productions. Since we are joining two tables and not more, for simplicity we can use a join without the JOIN operator, listing the tables separated by commas, and using the word WHERE to indicate the join condition.

With the HAVING operator we use the aggregate function COUNT to count the number of roles in each production. The entire query for determining performances with one role, and therefore one actor, will be as follows:

Write queries with the SQL HAVING statement yourself, and then look at the solutions

Example 9. We continue to work with the Theater database. Display a list of actors who play more than one role in one performance and the number of their roles.

Use the JOIN operator. Naturally, use HAVING, GROUP BY.

Relational Databases and SQL Language

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records returned by the GROUP BY clause.

HAVING clause syntax

aggregate_function may be a function like SUM, COUNT, MIN, or MAX.

Example of using the SUM function
For example, you can use the SUM function to look up the department name and the sales amount (for the relevant departments). The HAVING offer can only select those departments whose sales are more than $1000.

SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000 ;

Example of using the COUNT function
For example, you can use the COUNT function to retrieve the name of the department and the number of employees (in the relevant department) who earned more than $25,000 per year. The HAVING proposal will select only those departments where there are more than 10 such employees.

Example of using the MIN function
For example, you can use the MIN function to return the department name and the minimum revenue for that department. The HAVING proposal will return only those departments whose revenue starts at $35,000.

SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) = 35000 ;

Example of using the MAX function
For example, you can also use the function to retrieve the department name and the department's maximum revenue. The HAVING proposal will only return those departments whose maximum revenue is less than $50,000.

SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary)< 50000 ;

It has in its arsenal many powerful tools for manipulating data stored in the form of tables.

Undoubtedly, the ability to group data when sampling it according to a certain criterion is one of these tools. HAVING, along with the WHERE operator, allows you to determine the conditions for selecting data that has already been grouped in some way.

HAVING SQL parameter: description

First of all, it is worth noting that this parameter is optional and is used exclusively in conjunction with the GROUP BY parameter. As you remember, GROUP BY is used when aggregate functions are used in SELECT, and the results of their calculations need to be obtained for certain groups. If WHERE allows you to set selection conditions before the data is grouped, then HAVING contains conditions relating to the data directly in the groups themselves. For a better understanding, let's look at the example with the circuit presented in the figure below.

This is an excellent example that gives HAVING SQL a description. A table is given with a list of product names, companies that produce them, and some other fields. In the query in the upper right corner, we are trying to get information about how many product items each company produces, and in the result we want to display only those companies that produce more than 2 items. The GROUP BY parameter formed three groups corresponding to company names, for each of which the number of products (rows) was calculated. But the HAVING parameter, with its condition, cut off one group from the resulting sample, since it did not satisfy the condition. As a result, we get two groups corresponding to companies with 5 and 3 production quantities.

One might wonder why use HAVING when SQL has WHERE. If we used WHERE, it would look at the total number of rows in the table, not by groups, and the condition would not make sense in this case. However, quite often they coexist perfectly in one request.

In the example above, we can see how data is first selected by the names of employees specified in the WHERE parameter, and then the result grouped in GROUP BY undergoes an additional check on the salary amount for each employee.

SQL HAVING parameter: examples, syntax

Let's look at some features of the HAVING SQL syntax. The description of this parameter is quite simple. Firstly, as already noted, it is used exclusively in conjunction with the GROUP BY parameter and is specified immediately after it and before ORDER BY, if there is one in the request. This is understandable, since HAVING defines conditions for already grouped data. Second, only the aggregate functions and fields specified in the GROUP BY parameter can be used in the condition of this parameter. All conditions in this parameter are specified in exactly the same way as in the case of WHERE.

Conclusion

As you can see, there is nothing complicated in this operator. Semantically, it is used in the same way as WHERE. It is important to understand that WHERE is used with respect to all selected data, and HAVING is used only with respect to the groups defined in the GROUP BY parameter. We have presented a comprehensive description of HAVING SQL, which is enough for you to confidently work with it.

In the previous article we looked at. There I wrote that this construction allows you to select separate groups and for each group calculate the functions specified after SELECT. A HAVING allows, according to the results of executing functions, to filter out unnecessary rows from groups. Let's look at this in more detail.

Let's remember our previous problem, where we calculated the average price of milk for a specific supermarket chain. Let’s not just look at the average price, but also list only those supermarket chains where average price below 38.

For this filtering based on the results of executing the aggregate function, we use in SQL command HAVING:

SELECT `shop_id`, AVG(`price`) FROM `table` GROUP BY `shop_id` HAVING AVG(`price`)< 38

As a result, instead of 4 we will only have lines 3 :

shop_id	AVG(`price`)
1	37.5
2	36.0
3	37.0

If designs GROUP BY it won't be then HAVING will not apply to a specific group, but to the entire sample. This means that if the condition HAVING will be executed, it will not have any effect. And if it is not executed, then there will not be a single resulting row.