Group by Clause :-
- group by clause is used to group records based on one (or) more columns to calculate Aggregates like max ,min,avg,sum etc.
- using group by we can produce summarized data from detailed data.
Syntax :- Select <collist> from<tabname> [where<cond>] Group by <collist>[having<cond>] [order by <collist>]
Display max salaries for each department
Select deptno, max(sal) maxsal from emp group by deptno
Output is below:
- Depno max sal
- 10 5000
- 20 3000
- 30 2850
- 40 1300
- 1 A 5000 10
- 2 B 4000 20
- 3 C 3000 10
- 4 D 3000 20
- 5 E 6000 30
- 10 5000
- 20 4000
- 30 6000
Display max sal,minsal,sum sal,no of employee working for each job.
Select job,max(sal) maxsal,min(sal),min sal sum (sal) sumsal,count(*) no of emps from emp group by job
Output is below:
- Job maxsal minsal sumsal no of emps
- Analyst 3000 3000 6000 2
- Clerk 2000 800 6150 5
Display no of employees joined in each year
Select year (hiredate) year,count (*) no of emps from emp group by year (hiredate)
- Year no of emps
- 1980 2
- 1981 10
- 1982 23
Number of employees joined in each day
Select date name (dw, hiredate) day, count(*) no of emps from emp group by datename(dw,hiredate)
Output is below:
- Day number of emps
- Friday 5
- Saturday 1
Display max salaries for each department whose department number is equal to 10 (or) 30
Select deptno,max(sal),maxsal from emp where deptno in(10-30) group by deptno
Output is below:
- Dept no maxsal
- 10 5000
- 30 8250
Display max salaries for each department where department number is equal to 10 (or) 30 and max sal ≥ 3000.