Group by clause in MS SQL Server

Group by clause will be discussed in detail in this article.

There are tons of data present in the database system. Even though the data is arranged in form of a table in a proper order, the user at times wants the data in the query to be grouped for easier access. To arrange the data(columns) in form of groups, a clause named group by has to be used in the query. The group by clause arranges the data according to the columns specified in the query. The basic syntax is-

Syntax :

select select_list 
from table_name 
group by column_1 column_2 

An example is given below-Student table :

select name
from student 
group by roll number 

Output –

This way, the table can be grouped using group by clause. In real-time production, group by clause is used for generating calculations by applying aggregate functions(maximum, minimum, etc). The users often confuse group by and order by clauses. Order by clause is used to arrange the data in chronological order. Group by clause is used to arrange the data in form of groups.

For better understanding, an example is given below.

Queries using order by and group by –

select  roll number
from student 
order by name ASC 

Output –

select roll number
from student 
group by  name 

Output –

From the example, we can clearly notice the difference between group by and order by clauses. In case of order by, the names are arranged in an alphabetical order(A-Z). In case the user has to arrange from Z-A, it can be done as follows.

select 
roll number 
from 
student
order by
name DESC

The output will be arranged as follows :