query using where,group by,having

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • AdusumalliGopikumar
    New Member
    • Aug 2007
    • 42

    query using where,group by,having

    can anybody write a query using where,group by ,and having and explain me
  • Saii
    Recognized Expert New Member
    • Apr 2007
    • 145

    #2
    Can you please post the query you wrote related to the same and if there is anything missing, anyone can explain.

    Comment

    • qhjghz
      New Member
      • Aug 2007
      • 26

      #3
      Originally posted by AdusumalliGopik umar
      can anybody write a query using where,group by ,and having and explain me

      select empno,count(*) from emp group by empno having count(empno)>1

      This query finds out the rows in emp with same empno

      Comment

      • AdusumalliGopikumar
        New Member
        • Aug 2007
        • 42

        #4
        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

        • qhjghz
          New Member
          • Aug 2007
          • 26

          #5
          Originally posted by AdusumalliGopik umar
          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));
          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 affexts performance. But see the query I posted.

          Comment

          • qhjghz
            New Member
            • Aug 2007
            • 26

            #6
            Originally posted by AdusumalliGopik umar
            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));

            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 clear

            Comment

            Working...