Group by in DBMS

Group By in DBMS

 

In this article, we will learn about Group By in DBMS.

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.

Group By in DBMS

General Syntax for GROUP BY in DBMS

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

Consider a sample table ’emp’

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020

Display the number of employees present in each department

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

O/P

3 rows selected.
DEPTNOCOUNT(*)
301
102
204

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.
DEPTNOMAX(SAL)
302850
105000
203000

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.
DEPTNOMAX(SAL)
105000
203000

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
DEPTNOMAX(SAL)
105000
203000
302850

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.
ENAMESUM(SAL)
JONES2975
KING5000
CLARK2450
SCOTT3000
BLAKE2850
FORD3000