MS SQL Server: GROUP by Clause in SQL Server

The GROUP BY clause in SQL Server (Transact-SQL) is used in the SELECT statement to retrieve data from multiple records and result groups into 1 or more columns.

GROUP BY clause clause in SQL Server

 SELEC T bieuthuc1, bieuthuc2, … bieuthuc_n, 
  ham_tong (bieuthuc) 
 FROM bang 
 [WHERE dieukien] 
 GROUP BY bieuthuc1, bieuthuc2, . bieuthuc_n; 

Variable name or variable value

bieuthuc1, bieuthuc2,. bieuthuc_n

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

ham_tong

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

state

The table to retrieve records from, must have at least 1 table in the FROM clause.

WHERE dieukien

Option. The condition that the record must meet to be selected is in the WHERE clause.

As an example, - use the SUM function

 SELECT ten _sanpham, SUM(soluong) AS 'Tong so luong' 
 FROM sanpham 
 GROUP BY te n_sanpham; 

This example uses the SUM calculation function to return the product name and total quantity (by product name).

Because you give a column (ten_sanpham) in the SELECT statement and not in the SUM function, you must use the GROUP BY clause.

As an example, - use the COUNT function

 SELECT related ly_id, COUNT (*) AS 'So nhan vien' 
 FROM nhanvien 
 WHERE ho = 'Anderson' 
 GROUP BY qua nly_id; 

In this example, the COUNT function will return quanly_id and the number of employees with the last name is Anderson.

As an example, - use the MIN function

 SELEC T loai_sanpham, MIN(soluong) AS 'So luong it nhat' 
 FROM sanpham 
 GROUP B Y loai_sanpham; 

The GROUP BY clause used with the MIN function above will return the product type and the minimum quantity for that product type.

Lesson: HAVING clause in SQL Server