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
From Emp:
  •                         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.