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-
select select_list from table_name group by column_1 column_2
An example is given below-Student table :
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
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
select roll number from student group by name
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 :
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.
- Difference between Having clause and Group by clause
- Difference between order by and group by clause in SQL
- Distinct clause in MS SQL Server
- Where clause in MS SQL Server
- Having clause in MS SQL Server
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | WITH clause
- SQL | Except Clause
- SQL | OFFSET-FETCH Clause
- Having vs Where Clause in SQL
- SQL | LIMIT Clause
- SQL | Intersect & Except clause
- SQL | USING Clause
- SQL | With Ties Clause
- SQL | Sub queries in From Clause
- SQL | ON Clause
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.