Printable View
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?
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
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?
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;
[quote=jamesravid;1159]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[/quote]
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
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.