can anybody write a query using where,group by ,and having and explain me
query using where,group by,having
Collapse
X
-
Tags: None
-
A query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptn o,sal
from emp
where sal>=2500
group by deptno,job,sal, ename
having deptno =10
order by ename desc;
or
select ename,sal
from emp
where sal=(select avg(sal)
from emp
group by deptno
having deptno not in(10,20));Comment
-
Originally posted by AdusumalliGopik umarA query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptn o,sal
from emp
where sal>=2500
group by deptno,job,sal, ename
having deptno =10
order by ename desc;
or
select ename,sal
from emp
where sal=(select avg(sal)
from emp
group by deptno
having deptno not in(10,20));Comment
-
Originally posted by AdusumalliGopik umarA query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptn o,sal
from emp
where sal>=2500
group by deptno,job,sal, ename
having deptno =10
order by ename desc;
or
select ename,sal
from emp
where sal=(select avg(sal)
from emp
group by deptno
having deptno not in(10,20));
I won't suggest the queries you have given .. Just think what extra are you doing with the HAVING caluse which you could not have done with your WHERE clause. Nothing. Don't use HAVING in this regard. It affects performance. But see the query I posted. There was no alternative way to do it via WHERE clause because it was FILTERING BASED ON THE AGGREGATE(ie the count). The filtering was NOT ROW BY ROW in that query. REMEMBER THAT HAVING IS AN AGGREGATE CLAUSE. I hope this makes things more clearComment
Comment