Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    26

    Unanswered: Having clause without GROUP BY clause?

    Hi,

    What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

    eg :

    SELECT SUM(col1) from test HAVING col2 < 5

    SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

    I want the equivalent query in MSSQLServer for the above Oracle query.

    Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

    Thanks,
    Gopi.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those queries actually run in oracle? i rather doubt it

    without a GROUP BY, the entire table is considered a single group

    the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

    in the second query you will surely get a syntax error even in oracle

    perhaps what you want for the two queries is:

    SELECT SUM(col1) from test where col2 < 5

    SELECT SUM(col1) from test WHERE x=y AND col2 < 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    26
    Sorry for the typos. Actually the queries are as follows.


    SELECT SUM(col1) from test HAVING SUM(col2) < 5

    SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

    Thanks,
    Gopi.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have sql server? if so, why don't you test those queries and see what you get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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