MS SQL Server: 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 instance, - 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 instance, - 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 instance, - 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 instance, - use the MAX function

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