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;
