Results 1 to 6 of 6

Thread: GROUP_BY and HAVING clause in SQL

  1. #1
    Contributing Member
    Join Date
    Jul 2006
    Answers
    76

    GROUP_BY and HAVING clause in SQL

    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?


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: GROUP_BY and HAVING clause in SQL

    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


  3. #3
    Contributing Member
    Join Date
    Jul 2006
    Answers
    76

    Re: GROUP_BY and HAVING clause in SQL

    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?


  4. #4
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    Re: GROUP_BY and HAVING clause in SQL

    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;


  5. #5
    Junior Member
    Join Date
    Sep 2008
    Answers
    1

    Re: GROUP_BY and HAVING clause in SQL

    Quote Originally Posted by jamesravid View Post
    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

    Last edited by umesh.sureban; 09-08-2008 at 07:07 AM.

  6. #6
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: GROUP_BY and HAVING clause in SQL

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact