Group By in DBMS

Simple example for GROUP BY

Consider a sample table ’emp’

Display the number of employees present in each department

select deptno,count(*) 
from emp 
group by deptno;

O/P

3 rows selected.

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.

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.

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

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.