# Databases > SQL GROUP_BY and HAVING clause in SQL

## nancyphilips

I had a reading about GROUP_BY and HAVING clause in Structured Query Language namely SQL. But what I wanted to know is if one uses both these clauses together namely if I want to use GROUP_BY and HAVING clause together is it possible. If not what are the restrictions I have?

----------


## jamesravid

Yes. You can use Group by and Having clauses together. You can say "Having clause is child clause of Group by clause". That means you can't use having clause with out Group by.

If you are asked to display department id and total salary of each department then you will write,

select department_id, sum(salary) 
  from employees 
  group by department_id;

the above statement works happily.  But if you are asked to display department id and total salary of each department whose total salary is greater than $50,000 then How will you do that? can we use where clause for this? No. We can not use where clause here. Bacause Where clause is meant for filtering records not groups. Here we have to filter groups. 

The solution to this is Having clause. Having clause is used to filter groups.


select department_id, sum(salary) 
  from employees 
  group by department_id
  having sum(salary) >50000;

The above sql filters departments whose total salary is less than $50,000.

I hope you are clear with the Having clause now. :Smile:  

---James

----------


## nancyphilips

Yes it gave me a very clear explanation that group by clause can occur without having clause but having clause cannot occur without group by. Can you tell me whether is this only restriction or is there any more with aspect to the above two clauses. If there is any more kindly provide a good insight as above which would help me?

----------


## chaitueeturi

Having clause can occur without groupby clause.But the entire records in the table are considerd as single group and we can get the corresponding o/p

observe this eg:

select sum(sal) from emp
having sum(sal)>10000;

----------


## umesh.sureban

> yes. You can use group by and having clauses together. You can say "having clause is child clause of group by clause". That means you can't use having clause with out group by. If you are asked to display department id and total salary of each department then you will write, select department_id, sum(salary) from employees group by department_id; the above statement works happily. But if you are asked to display department id and total salary of each department whose total salary is greater than $50,000 then how will you do that? can we use where clause for this? no. We can not use where clause here. Bacause where clause is meant for filtering records not groups. Here we have to filter groups. The solution to this is having clause. Having clause is used to filter groups. Select department_id, sum(salary) from employees group by department_id having sum(salary) >50000; the above sql filters departments whose total salary is less than $50,000. I hope you are clear with the having clause now. ---james


hi james, can we display other than groupby function column also. Ex. I have to display ename,empno,deptno from emp table. But it should display the dept where employees are more than 5 .
 Emp table as below: 
sql> select ename,empno,deptno from emp; 
ename empno deptno ---------- ---------- ---------- 
smith 7369 20 
allen 7499 30 
ward 7521 30 
jones 7566 20 
martin 7654 30 
blake 7698 30 
clark 7782 10 
scott 7788 20 
king 7839 10 
turner 7844 30 
adams 7876 20 
ename empno deptno ---------- ---------- ---------- 
james 7900 30 
ford 7902 20 
miller 7934 10 
14 rows selected. 

Awaiting your response for above query 
regards umesh

----------


## krishnaindia2007

Here is the query

select ename,empno,deptno from emp
where  deptno in 
(select deptno from emp
group by deptno
having count(empno)>5
)


You can get the same result using analytical functions also.

----------

