Skip to content
Related Articles

Related Articles

Improve Article
Group by clause in MS SQL Server
  • Last Updated : 24 Aug, 2020

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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE


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 –

NameRoll number
Riya111
Apoorva112
Mina113
Rita114
Veena115
Deepa116

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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE



Queries using order by and group by –

select  roll number
from student 
order by name ASC 

Output –

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

select roll number
from student 
group by  name 

Output –

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

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 numberName
115Veena
111Riya
114Rita
113Mina
116Deepa
112Apoorva


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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :