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.
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’
Display the number of employees present in each department
select deptno,count(*) from emp group by deptno;
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
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
select deptno,max(sal) from emp where deptno!=30 group by deptno;
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
select deptno,max(sal) from emp group by deptno order by deptno;
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 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
SELECT ename , SUM(sal) FROM emp GROUP BY ename HAVING SUM(sal)>2000;
6 rows selected.