GROUP BY clause in SQL

GROUP BY clause in SQL is used data arrangement or sorting by specific condition. When GROUP BY clause is used, first the data gets divided into groups based on the column specified in the class then the group function gets applied once on each group to get the final result.

Example-GROUP BY clause on single column:

Select Deptnumber, Job, Min (Sal) from BANK GROUP BY Job

Above query retrieves least salary corresponding to each job.

Note: We can apply the GROUP BY clause on one or more columns.

Example-GROUP BY clause on multiple columns:

Select Deptnumber, Job, Max (Sal) from BANK GROUP BY Deptnumber, Job

Above query retrieves the highest salary corresponding to each job of each department.

In the above query, data first gets grouped based on the first column of the clause and then each group is sub-divided based on the second column of the clause. Now the group function gets applied on each inner group to get the final result.

While using the GROUP BY clause the select list can contain only the following unit:

1.        Group functions
2.        Columns that are present in GROUP BY clause
3.        Constants

Example:

Select GetDate (), Deptnumber, Max (Sal) from EMP GROUP BY Deptnumber

Select Count (*) from EMP GROUP BY Deptnumber