HomeProgramSQL

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

4 ★ | 1 Vote
learn SQLSQLGROUP BY clause

You should read it

May be interested

  • How to group in Word, group multiple shapes into 1 in WordHow to group in Word, group multiple shapes into 1 in Word
    how to group in word, group multiple shapes into 1 in word. the group feature (group) in word helps you group shapes into a unified block, making moving a group of objects easier without fear of affecting the arrangement in the
  • Bitsadmin getdisplayname and bitsadmin geterror command in WindowsBitsadmin getdisplayname and bitsadmin geterror command in Windows
    the bitadmin getdisplayname command takes the display name of the specified task. the bitsadmin geterror command retrieves detailed error information for the specified job.
  • The echo command in WindowsThe echo command in Windows
    the echo command displays the message or turns on / off the command repeat feature. if used without parameters, the echo command will display the current echo setting.
  • Fc command in WindowsFc command in Windows
    the fc command compares two files or a collection of files and displays the differences between them.
  • Command at in WindowsCommand at in Windows
    the at command schedules commands and programs to run on a computer at a specified time with a specific date. you can only use the command at when schedule service is running.
  • Doskey command in WindowsDoskey command in Windows
    the doskey command calls doskey.exe (which reminds of previously entered command lines), modifies the command line and creates macros.
  • 10 useful commands in Windows you should know10 useful commands in Windows you should know
    command prompt is used to execute batch files, perform tasks quickly, help you troubleshoot and solve some windows problems when the system crashes. however, not all commands in windows are useful and must be done regularly. with the 10 command lines in the following article it is very useful even if you are not an administrator.
  • How to set iMessage group wallpaper on iPhoneHow to set iMessage group wallpaper on iPhone
    ios 26 now lets you set custom backgrounds for individual imessage group chats. this new feature gives you a choice of colorful backgrounds to choose from if you want to customize the look of your group chat.
  • How to use the which command in LinuxHow to use the which command in Linux
    the which command in linux determines the executable binary, which will execute when you issue a command to the shell. if you have different versions of the same program on your computer, you can use which to find out which shell will use.
  • New malware uses Google Drive as a command-and-control serverNew malware uses Google Drive as a command-and-control server
    network security researchers have now discovered a new malware attack campaign linked to the notorious aph darkhydrus group, which uses google drive as a command and control server.

SQL