GROUP BY command in SQL

In SQL, the GROUP BY clause is used in conjunction with the SELECT statement to sort data from multiple identical records into groups.

GROUP BY follows the WHERE clause in the SELECT statement and precedes the ORDER BY clause.

In this article, we'll show you in detail how to use the GROUP BY clause in SQL with syntax and specific examples to make it easier to visualize and capture statements.

The GROUP BY command syntax in SQL

GROUP BY basic syntax in SQL is as follows:

 SELECT cot1, cot2 
 FROM ten_bang 
 WHERE [ dieu_kien ] 
 GROUP BY cot1, cot2 
 ORDER BY cot1, cot2 

Note the GROUP BY clause must follow the conditions in the WHERE clause and precede the ORDER BY clause if used.

Example of GROUP BY in SQL

Suppose the NHANVIEN table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

If you want to know the total salary of each employee, the GROUP BY query will look like this:

  SQL> SELECT TEN, SUM(LUONG) FROM NHANVIEN 
  GROUP BY TEN; 

The result is:

 +----------+----------+ | TEN |SUM(LUONG)| +----------+----------+ | Cao | 4500.00 | | Huy | 8500.00 | | Lam | 10000.00 | | Loan | 1500.00 | | Manh | 6500.00 | | Nga | 2000.00 | | Thanh | 2000.00 | +----------+----------+

Now, we have the following table with copies of duplicate names:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Thanh | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Nga | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

If you want to know the total salary of each employee, the current GROUP BY query will look like this:

 SQL> SELECT TEN, SUM(LUONG) FROM NHANVIEN 
  GROUP BY TEN; 

The result is:

 +---------+-------------+ | TEN | SUM(LUONG) | +---------+-------------+ | Huy | 8500.00 | | Nga | 8500.00 | | Cao | 4500.00 | | Lam | 10000.00 | | Thanh | 3500.00 | +---------+-------------+ 

In the next section, we will learn about the keyword DISTINCT, remember to follow it.

Previous article: ORDER BY command in SQL

Next lesson: DISTINCT keyword in SQL