Group by in DBMS

Group by clause

If you want to display the total salaries of each department in a company or else to display the highest paid employees of each branch of that company. this purpose is also called a by using a
group by clause

Group by clause in SQL used to arrange logically related data into groups with help of some functions i.e  if a particular column has the same type of data in different rows then they can be organized this into a logical groups

General Syntax for GROUP BY clause

SELECT column_name(s),function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Simple example for GROUP BY

GroupBy syntax
Consider a sample table ’emp’
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

Display the number of employees present in each department

select deptno,count(*) 
from emp 
group by deptno;
O/P
3 rows selected.
DEPTNO COUNT(*)
30 1
10 2
20 4
The above query display group wise count of each department

Display the highest played employees in each department

select deptno,max(sal) 
from emp
group by deptno
O/P
3 rows selected.
DEPTNO MAX(SAL)
30 2850
10 5000
20 3000
Department wise  highest salary i.e logically categorizing category employees into department wise

Using  WHERE  clause in the GROUP BY

Using where clause  rows  can be pre excluded before dividing them into groups, where clause  must be specified before the  group by clause  when it is used in the query

Example

select deptno,max(sal)
from emp
where deptno!=30
group by deptno;
O/P
2 rows selected.
DEPTNO MAX(SAL)
10 5000
20 3000
Highest salaries of all departments expect deptno 30 are displayed

Using ORDER BY with GROUP BY

We can also display the rows in sorted order after logical organizing into groups using order by clause along with group by clause

Example

select deptno,max(sal) 
from emp
group by deptno
order by deptno;
O/P
3 ROWS SELECTED
DEPTNO MAX(SAL)
10 5000
20 3000
30 2850
The above query displays the highest salaries in each department but first deptno 30 details followed by deptno 20 and deptno 30 i.e  increasing order of department numbers

Points to note about GROUP BY  clause

  • GROUP BY clause is used only with the SELECT statement.
  • Where class is placed before group by class if it is used in the query.
  • Order by class is placed after the group by class if it is used in the query group by class
  • All the columns that are used in the select statement must be specified by using group by clause 
  • If a group function is included in the select clause then we cannot use individual result columns

HAVING  clause

  • Having Clause is used to place conditions and decide which group will be part of the final result
  • You cannot use aggregate functions like sum() count() etc with  where clause
  • Hence we need to use having clause if you want to specify conditions using this aggregate functions

Example

SELECT ename , SUM(sal) FROM emp
GROUP BY ename
HAVING SUM(sal)>2000;
O/P
6 rows selected.
ENAME SUM(SAL)
JONES 2975
KING 5000
CLARK 2450
SCOTT 3000
BLAKE 2850
FORD 3000