SQL GROUP BY AND HAVING CLAUSE WITH EXAMPLES

This is one of the important topic in SQL,we have discussed joins in SQL already. SQL includes many aggregate functions,which take information from multiple rows and aggregate it down to produce fewer rows in the final result set."It is used to group the results from one or more tables".

When to use Group By clause?

In fact, if you use select statement  the entire table records  formed as one group by group functions.When we need aggregations like below,we have to use Group by Clause:

• Department-wise total salary of employees
• Month-wise count of employees joined
• Account type and branch-wise average balance maintained

Syntax:

SELECT column_name,function(column_name)

from table_name

GROUP BY column_name

Here is i am taking employee table from them list out job designation_wise number of employees working and total salary from employee table

From the above employee table list out the  job designation_wise and total salary of employees.

Example:

SELECT job,count(*) no_of_employees,sum(sal) total_salary from Employee e

GROUP BY job;

Here job is the column by which rows of employees table are grouped

Result will be:

Having Clause:

The Having clause allows us to filter out groups that do not meet a specific requirement.It is used to mention condition in GROUP based SQL function like WHERE clause.

WHERE clause is executed on actual rows of the table being required.

Syntax:

SELECT column_name,function(column_name)

from table_name 

WHERE column_name condition

GROUP BY column_name

Having function(column_name)condition

Now, we will list out  from the above employee table  department wise no of employees and average salary,of those employees who do not get a commission and only for those departments in which employees get a at least a salary of 25000 or above.

Example:

SELECT Dept_no,count(*)no_of_employees,avg(sal)Avg_sal

from employees

where COM is NULL

GROUP BY Dept_no

Having Min(sal)>=20000

Order By Avg_sal DESC