HAVING SQL operator

HAVING SQL operator is used in combination with GROUP BY operator to limit groups of returned strings only to those whose condition is TRUE.

Syntax of the HAVING operator in SQL

SELECT expr1,2, ..._n,
 aggreg_function (aggreg_expression)
 FROM tabs
 [WHERE conds]
 GROUP BY1, 2, ..._n
 HAVING cond;

where:

• expr1,2,… – Expressions that are not contained in the aggregate function should be included in the GROUP BY offer at the end of the SQL statement.
• aggreg_function – This is an aggregate function such as SUM, COUNT, MIN, MAX or AVG.
• aggreg_expression – This is the column or expression for which aggregate_function will be used.
• tabs – The tables from which you want the records. The FROM sentence must contain at least one table.
• WHERE conds – It’s optional. These are the conditions for selecting records.
• HAVING cond – This is another condition that applies only to aggregate results to limit groups of returned strings. Only groups whose state is evaluated as true will be included in the result set.

HAVING operator – using SUM function

Let’s look at the SQL example HAVING, which uses the SQL function SUM.

You can also use SUM SQL function to return the name of department and total sales in a related department. The HAVING SQL proposal will filter the results so that only department with sales over $1000 will be returned.

SELECT depart, SUM(sales_id) AS "Total sales_id"
    FROM order_dets
  GROUP BY depart
HAVING SUM(sales_id) > 1000;

HAVING SQL – use COUNT function

Let’s see how we can use HAVING with the COUNT SQL function.

You can use the COUNT SQL function to return the name of a department and the number of employees in the corresponding department whose sales are over $25000 per year.

The HAVING SQL offer will filter the results, so only departments with more than 10 employees will be returned.

SELECT depart,
    COUNT(*) AS "Number of empls_id"
   FROM empls_id
 WHERE salary_id > 25000
 GROUP BY depart
HAVING COUNT(*) > 10;

HAVING SQL – use MIN function

Let’s now see how we could use HAVING with the MIN SQL function.

You can also use the SQL function MIN to return department names and MIN(salary_id). The HAVING SQL offer will only return departments where the MIN(salary_id) exceeds $55000.

SELECT department, MIN(salary_id) AS "Lowest salary_id"
   FROM empls
  GROUP BY depart
HAVING MIN(salary_id) > 55000;

HAVING SQL – use the MAX function

Finally, consider using HAVING with the SQL MAX function.

For example, you can use the SQL function MAX to return the department and the maximum salary in the department. The SQL proposal HAVING will return only those departments whose maximum salary is less than $35000.

SELECT depart,
    MAX(salary_id) AS "Highest salary_id"
   FROM empls
  GROUP BY depart
HAVING MAX(salary_id) < 50000;

HAVING clause and difference with GROUP BY & WHERE clause in SQL statement

Using HAVING clause and difference with GROUP BY & WHERE clause in SQL statement: