Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: having clause without group by ?

    Hi,

    What does having cluase meant without the combination of "GROUP BY" clause ?

    eg :

    select sum(col1) from test having col2 < 5

    What is the difference between having the above condition in a "Where Clause" and in a "Having clause" ?

    Please explain.

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would expect that SQL to fail, since HAVING applies to the result set after aggregation, which does not contain a col2. HAVING is intended to be applied to the aggregate values themselves, e.g. "HAVING sum(col1) > 0", but can be applied to any of the GROUP BY columns too (though WHERE makes more sense and may perform much better for them).

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    It is valid, and applies a summary-level filtering condition on the whole query (since there are no individual groups). It's just not particularly meaningful or useful.
    Code:
    SQL> SELECT COUNT(*) FROM user_objects ;
    
      COUNT(*)
    ----------
           158
    
    1 row selected.
    
    SQL> SELECT COUNT(*) FROM user_objects HAVING COUNT(*) = 1;
    
    no rows selected
    
    SQL> SELECT COUNT(*) FROM user_objects HAVING COUNT(*) > 1;
    
      COUNT(*)
    ----------
           158
    
    1 row selected.
    
    SQL> SELECT COUNT(*) FROM user_objects HAVING COUNT(*) = 158;
    
      COUNT(*)
    ----------
           158
    
    1 row selected.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SamCute's query was not valid, because it was more like this:
    Code:
    SELECT COUNT(*) FROM user_objects HAVING object_id < 5
    ... which results in "ORA-00979: not a GROUP BY expression"

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Good point. I guess I read the title and skimmed the example...

    HAVING must refer to either an item in the GROUP BY list or an aggregate such as COUNT(*).

    If there is no GROUP BY that only leaves aggregate expressions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •