HAVING CLAUSE IN SQL SERVER

Dept Table

Having clause

To filter data before group by we use where clause

To filter data after group by we use clause having

In condition if there is no aggregate function then use where clause

In condition if there is aggregate function then use having clause

Display maxsalaries for each dept. where deptno is equal to 10 and 30 and maxsal>3000

Select deptno, max (sal) from EMP where deptno in (10, 30) group by deptno having max (Sal)>=3000

Find total and avg sal paid to EMP deptwise only for the dept in which they are more than 2 EMP

Select deptno, sum (sal), avg (sal), count (*) from EMP group by deptno having count (*)>2

Find the no of EMP working for each dept only if no greater than 3

Select deptno, count (*) from EMP group by deptno having count (*) >3

Find the highest and lowest sal paid to EMP jobwise only for the job whose avg sal more than 2000

select job, avg(sal),max(Sal) ,min(Sal) from  emp group by job having avg(Sal)>2000

WRITE a query to find no of clerks working in each dept if the count is greater than 1

Select deptno, count (*) from EMP where job='clerk' group by deptno having count (*)>1

Display no of EMP joined in each year when year=1981 or 1982 and no of EMP >2

select year(hiredate) ,count(*) from emp where year(hiredate) in (1981,1982) group by year(hiredate) having count(*)>2

Display dept wise sum salaries and within the dept jobwise

Select deptno, job, sum (sal) from EMP group by deptno, job order by deptno, job

Write a query to find no of clerks working in each dept?

Select deptno, count (*) from EMP where job=’clerk’ group by deptno