GROUP BY clause in SQL Server

Group by in SQL Server What is it used for? Here it is everything you need to know about Groups by SQL Server.

SQL Server is knowledge that every programmer needs to know. It's not a programming language. It is a means to help you manage data effectively. Code and data are the roots of programming, so managing them is very important.

Learning how to use SQL Server is not difficult. Let's start with the most basic knowledge introduced on Quantrimang.com. In this article, let's learn together what the Group by command is!

Group by command in SQL Server is used to sort identical data into groups with the help of some other functions. For example, if a particular column has the same value as different rows, it sorts those rows into a group.

Outstanding features of Group by in SQL Server

• The Group by clause is used with the Select command.
• In this query, the Group by statement is placed after the WHERE clause.
• In this query, the GROUP BY clause is placed before the Having statement.

Syntax:

SELECT column1, column2,...columnN FROM table_name
[WHERE]
[GROUP BY column1, column2...columnN]
[HAVING]
[ORDER BY]

The SELECT clause can include columns used with SQL GROUP BY. Therefore, to include other columns in the GROUP BY function, use aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG() with those columns.

Outstanding features of GROUP BY in SQL:

• Used to group records.
• Comes after the WHERE clause if present and before HAVING.
• One or more columns can be included to form one or more groups based on that column.
• Only GROUP BY columns can be included in the SELECT clause. To use other columns in SELECT, use an aggregate function.

To make it easier for you to follow how to use GROUP BY in SQL Server and as an example, we will use the data table below throughout the article.

Table with names Quantrimang.comthere are 4 columns IDhuyenmuc, Muccon, Chuyenmuclon, Sobai and 6 rows as follows:

+------------+----------+-------------+-------+
|IDChuyenmuc |  Muccon  |Chuyenmuclon | Sobai |
+------------+----------+-------------+-------+
|    1       |SQL Server|Lap trinh    | 101   |
|    2       |Facebook  |Mang xa hoi  | 152   |
|    3       |Python    |Lap trinh    | 101   |
|    4       |JavaScript|Lap trinh    | 122   |
|    5       |Chrome    |Web          | 94    |
|    6       |Instagram |Mang xa hoi  | 165   |
+------------+----------+-------------+-------+

GROUP BY clause syntax in SQL Server

SELECT
    bieu_thuc1,  bieu_thuc2,...  bieu_thuc_n, 
       ham_tong_hop (bieu_thuc)
FROM
    ten_bang
[WHERE dieukien]
GROUP BY
    bieu_thuc1,
    bieu_thuc2,...;

Variable name or variable value:

• symbol1, symbol2,… symbol: The expression is not inside an aggregate function and must be in a GROUP BY clause.
• ham_tong_hop: Can be functions like SUM, COUNT, MIN, MAX or AVG.
• state_name: The table to get the records from, must have at least 1 table in the FROM clause.
• WHERE dieukien: Options. This is the condition that the record must meet to be selected.

Example – using the SUM function

SELECT Chuyenmuclon, SUM(Sobai) AS "Tong"
  FROM [Quantrimang.com]
  GROUP BY Chuyenmuclon;

This example uses the SUM function to return names Chuyenmuclon and total number of posts (Sobai) (by major category name).

Because you give 1 column (Chuyenmuclon) in the SELECT statement and not in the SUM function, so you will have to use the GROUP BY clause.

Then we have the following results table:

+--------------+------+
| Chuyenmuclon | Tong |
+--------------+------+
|Lap trinh     | 324  |
|Mang xa hoi   | 317  |
|Web           | 94   |
+--------------+------+

Example – using the COUNT function

SELECT  COUNT(Chuyenmuclon), Chuyenmuclon
  FROM [Quantrimang.com]
  WHERE Chuyenmuclon = 'Lap trinh'
  GROUP BY Chuyenmuclon;

In this example, the COUNT function will return the number of cycles of the Lap record:

+------+--------------+
|      | Chuyenmuclon |
+------+--------------+
|  3   |  Lap trinh   |
+------+--------------+

If you don't want to get the column name but just return the number of child items, run the following command:

SELECT  COUNT(Chuyenmuclon)
  FROM [Quantrimang.com]
  WHERE Chuyenmuclon = 'Lap trinh'
  GROUP BY Chuyenmuclon;

The returned result will only have the number 3.

Example – use the MIN function

SELECT Chuyenmuclon, MIN(Sobai) AS 'So luong it nhat'
 FROM [Quantrimang.com]
 GROUP BY Chuyenmuclon;

The GROUP BY clause used with the MIN function above will return the Chuyenmuclon and the smallest number of articles for that large item.

Example – using the MAX function

SELECT Chuyenmuclon, MAX(Sobai) AS 'So bai cao nhat'
 FROM [Quantrimang.com]
 GROUP BY Chuyenmuclon;

This last example returns the name of Chuyenmuclon and the highest post count of the category.

Example – Use with HAVING clause

If you are wondering how to filter results after grouping, the HAVING clause will help you solve this problem.

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Note, the HAVING clause follows GROUP BY. Otherwise, it's basically a simple replacement for WHERE with HAVING. Result:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

You can still use the WHERE condition to filter results before grouping. It will work together with the HAVING clause to filter after grouping:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

Results: Only one artist in the database released more than one album after 1990.

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Previous article: EXISTS condition in SQL Server

Next article: HAVING clause in SQL Server