SQL Group by Clause

Group by Clause

GROUP BY Clause :-GROUP BY clause is used with SELECT statement, for extracting the Group Functions Value.

Why we need to use Group By Clause :- For example in a School we want the count of every class then we need to create class-wise group so that we can count.
           Here count is a group function so when we need result of group function. We use grouping.
                          
Grouping Function (Group Function):- Below are the Grouping Functions

1) SUM
2) MIN
3) MAX
4) COUNT
5) AVG

Note:-1) Grouping Functions are also known as Multi-Row Function.These function work on group of records only.
      2) We can use Group Function without any grouping on table as table is also a group.
     
What is mandatory condition for Group By Clause:- Below are the mandatory condistion for grouping
      1) We create group on any column of table when that column have duplicates values otherwise no mean of that group
      2) What column we are using with group function that column should be in "Group By" Clause
     

GROUP BY Clause Syntax :-Below is the syntax for group by clause

SELECT
    column_name1, column_name2,.., aggregate_function(column_name)
    FROM table_name
    [ WHERE condition ]
    GROUP BY column_name1, column_name2,..;


GROUP BY Clause Examples:-  We can use grouping function with single or with multiple columns

Grouping Function with Single Column :-

Examples :- We have "EMP" table with below data

SQL> SELECT * FROM EMP;

Example1:- Display the department-wise employee count

SQL> SELECT deptno,count(*) FROM EMP GROUP BY deptno;

Example2:- Display the Job-wise employee count

SQL> SELECT job,count(*) FROM EMP GROUP BY job;

Example3:- Display the department-wise minimum salary of employee.

SQL> SELECT deptno,min(sal) FROM EMP GROUP BY deptno;

Example4:- Display the department-wise maximum salary of employee

SQL> SELECT deptno,max(sal) FROM EMP GROUP BY deptno;

Example5:- Display the department-wise avarage salary of employee 

SQL> SELECT deptno,avg(sal) FROM EMP GROUP BY deptno;

Example6:- Display the department-wise minimun,maximum and avarage salary of employee 

SQL> SELECT deptno,min(sal),max(sal),avg(sal) FROM EMP GROUP BY deptno;

Grouping Function with Multiple Columns :-

Example1 :-  Display the Department-wise and  Job-wise employee count

SQL>SELECT deptno,job,count(*) from EMP group by deptno,job;

Grouping Function without "Group By" Clause:- We can use Group Function without group by clause when we use group function in whole table

Example1:- Display the count of employee.

SQL> SELECT count(*) from EMP;

Example2:- Display the minimum and maximum salary of employees

SQL>SELECT min(sal),max(sal) from EMP;