GROUP by Command in SQL

This guide covers group by command in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

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