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 :

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE

In case a user wants to group data according to the roll number, it can be done as shown below-

select name
from student 
group by roll number 

Output –

Name	Roll number
Riya	111
Apoorva	112
Mina	113
Rita	114
Veena	115
Deepa	116

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.

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE

Queries using order by and group by –

select  roll number
from student 
order by name ASC 

Output –

Roll number	Name
112	Apoorva
116	Deepa
113	Mina
114	Rita
111	Riya
115	Veena

select roll number
from student 
group by  name 

Output –

Roll number	Name
111	Riya
112	Apoorva
113	Mina
114	Rita
115	Veena
116	Deepa

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 :

Roll number	Name
115	Veena
111	Riya
114	Rita
113	Mina
116	Deepa
112	Apoorva

This way, the arrangement can be modified. In the case of group by, the data is arranged in form of groups but not in chronological order. Here, the where clause is not being used as it generates an error.