Group by clause is a clause of select statement responsible to logically segregate the table data in different blocks. This clause is only meant for multirow function. In this topic, we will discuss one of the major clauses in SQL that is the group by clause in DQL(Data Query Language).
Please read the previous post
Introduction to Structured Query Language (SQL)
Object-Relational Database
SELECT Statement in SQL
This clause is only meant for multirow function.
Contents
Syntax:
Select <exp>
From<t_name>
[Where<cond>]
[group by<col_name>[,col_name,…………..n]];
Now we get more idea on group clause based on the below example
Q. Write a query which will display department wise sum(sal), max(sal), min(sal) ?
Select sum(sal), max(sal), min(sal)
From emp
Group by deptno;
NOTE:
Only that column is allowed in select list, the column which is used in group by clause.
Select ename, sum(sal), max(sal),min(sal)
From emp
Group by deptno;
The above line of SQL statement will get error because ename (i.e. Employee Name) is not directly depend up on department group.
Group By Clause for multiple column
Now we will working with multiple column wise grouping:
Q. Write a query which will display deptwise , job wise sum(sal),max(sal),min(Sal) ?
Select deptno, job , sum(sal),max(sal), min(sal) from emp
Group by deptno, job;
Count() function in SQL
Count is a multirow() responsible to count the number of values of a column or no. of records of a group. If can accept two types of arguments in function is *, col_name.
Select count(*) from emp;
--based on column name
Select count(mgr) from emp ;
Q. Write query which will display department wise , job wise, sum(sal),max(sal), min(Sal) and the no. of employee working in each group.
Select deptno, max(sal),min(sal),sum(sal), count(*) from emp
Group by deptno,job;
Reference Link:
SELECT – GROUP BY- Transact-SQL
