HAVING clause in SQL Server

The HAVING clause is used in conjunction with the GROUP BY clause in SQL Server (Transact-SQL) to limit the group of returned rows, only when the condition is met is TRUE.

HAVING clause syntax in SQL Server

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

Variable name or variable value

ham_tong

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

bieuthuc1, bieuthuc2, . bieuthuc_n

The expression is not within the total function and must be in the GROUP BY clause.

WHERE dieukien

Option. The conditions that the record must meet to be selected.

HAVING dieukien_having

This is an additional condition that applies only to the total result to limit the groups of returned rows. Only groups whose conditions are evaluated are TRUE in the result set.

For example - use the SUM function

 SELECT bophan, SUM  (soluong) AS 'Tong so luong' 
 FROM sanpham 
 GROUP BY bophan 
 HAVING SUM (soluong) > 1 00; 

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

For example - use the COUNT function

 SELECT th anhpho, COUNT (*) AS 'So nhanvien' 
 FROM nhanvien 
 WHERE bang = 'California' 
 GROUP BY thanhpho 
 HAVING COUN T (*)> 20; 

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

For 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 wage in each department. The HAVING clause will only return parts with a minimum wage greater than or equal to $ 50,000.

For example - use the MAX function

 SELECT h o, MAX (luong) AS 'Luong cao nhat' 
 FROM nhanvien 
 GROUP BY bophan 
 HAVING MA X (luong)> 34000; 

In this final example, the result is the employee's last name and the maximum salary for that family's value. The HAVING clause limits only the values ​​they have the maximum salary greater than $ 34,000.

Previous article: GROUP BY clause in SQL Server

The following article: SELECT TOP command in SQL Server