HAVING clause in SQL Server

What is Having in SQL? Do you know how to use Group by having in SQL? Let's find out with Quantrimang.com!

Having Command in SQL Server allows users to filter query results based on aggregate and group functions that cannot be achieved with the WHERE clause (used to filter individual rows).

To put it more simply, the Having statement in SQL Server is used to apply a filter on the results of GROUP BY based on specific conditions. These conditions are Boolean (for example, using logical operators – AND, OR). This clause is included in SQL because the WHERE keyword fails when used with aggregate expressions. Having is a very commonly used clause in SQL. Similar to WHERE, it helps apply conditions but HAVING works in groups. If you want to filter a group, this is when you need the HAVING function in SQL Server.

Some important points:

• The HAVING clause is used to filter data according to the provided conditions.
• The HAVING clause is often used in reports containing large data.
• The HAVING clause can only be used with the SELECT clause.
• This expression in syntax can only have constants.
• In queries, ORDER By is placed after the HAVING clause if present.
• The HAVING clause is implemented in column editing.
• The HAVING clause is often used after GROUP BY.

HAVING clause syntax in SQL Server

SELECT bieuthuc1, bieuthuc2, … bieuthuc_n,
       ham_tong (bieuthuc)
FROM bang
[WHERE dieukien]
GROUP BY bieuthuc1, bieuthuc2, … bieuthuc_n
HAVING dieukien_having;

Variable name or variable value

ham_tong

Can be functions like SUM, COUNT, MIN, MAX or AVG.

bieuthuc1, bieuthuc2, … bieuthuc_n

The expression cannot be inside an aggregate function and must be in a GROUP BY clause.

WHERE dieukien

Options. Conditions that a record must meet to be selected.

HAVING dieukien_having

This is an additional condition that applies only to total results to limit the groups of rows returned. Only groups for which the condition evaluates to TRUE are in the result set.

Example – using the SUM function

SELECT bophan, SUM (soluong) AS “Tong so luong”
FROM sanpham
GROUP BY bophan
HAVING SUM (soluong) > 100;

The HAVING clause example above uses the SUM function to return the name of the department and the total quantity (in the relevant department). The HAVING clause filters the results so that only parts with a quantity greater than 100 are returned.

Example – using the COUNT function

SELECT thanhpho, COUNT (*) AS “So nhanvien”
FROM nhanvien
WHERE bang = ‘California’
GROUP BY thanhpho
HAVING COUNT (*) > 20;

This example returns the city and the number of employees (in that city) who are currently in the state of California. The HAVING clause filters to return only cities with more than 20 employees.

Example – use the MIN function

SELECT bophan, MIN (luong) AS “Luong thap nhat”
FROM nhanvien
GROUP BY bophan
HAVING MIN (luong) >= 50000;

In this example, the returned result is the name of each department and the minimum salary in each department. The HAVING clause will only return departments with a minimum salary greater than or equal to $50,000.

Example – using the MAX function

SELECT ho, MAX (luong) AS “Luong cao nhat”
FROM nhanvien
GROUP BY bophan
HAVING MAX (luong) > 34000;

In this last example, the return is the employee's last name and the maximum salary for that last name value. The HAVING clause restricts only returning values ​​for families whose maximum salary is greater than $34,000.

Example – using the AVG() function

The following command finds product categories with an average list price between 500 and 1,000:

SELECT
    category_id,
    AVG (list_price) avg_list_price
FROM
    production.products
GROUP BY
    category_id
HAVING
    AVG (list_price) Giữa 500 và 1000;

Difference between WHERE clause and HAVing clause in SQL Server

The difference between WHERE and Having clauses in the database is a question that often appears in interviews.

The following table summarizes the difference between these two clauses in SQL Server. However, the main difference here is that the WHERE clause uses conditions to filter records before creating any group, while HAVING uses conditions to filter values ​​from a group.

Previous article: GROUP BY clause in SQL Server

Next article: SELECT TOP command in SQL Server