DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Select problem

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question

    What's wrong with this statement :

    SELECT department "Departments", MAX(salary) "Tope Salaries"
    FROM employee
    WHERE department IN(200, 300, 400)
    GROUP BY Departments
    HAVING MAX(salary) > 60000;


    Thanks


  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You can't use GROUPBY and HAVING in the SQL statement.

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    I can not figour out where is the mistake in your statement .
    I think it's correct . Groupby and Having are the basic in SQL .

  4. #4
    Join Date
    Nov 2000
    Posts
    212
    SELECT department "Departments", MAX(salary) "Tope Salaries"
    FROM employee
    WHERE department IN(200, 300, 400)
    GROUP BY Department[s!!!!]
    HAVING MAX(salary) > 60000;

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Is that Right ? Can we use GROUPBY and HAVING in the same statement ? There is some restriction, the order of usage of clauses or something,i don't remember anymore.
    someone clarify me. thanks

    [Edited by sreddy on 01-05-2001 at 08:59 AM]

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by sreddy [/i]
    [B]Is that Right ? Can we use GROUPBY and HAVING in the same statement ? There is some restriction, the order of usage of clauses or something,i don't remember anymore.
    someone clarify me. thanks

    [Edited by sreddy on 01-05-2001 at 08:59 AM] [/B][/QUOTE]

    Sreddy,

    The combination of GROUP BY and HAVING in the same selct is actually the most "natural" combination - you can say they were invented to work hand in hand from the very begining of SQL standard.

    As soon as you are using HAVING you must have a group expression and as soon as you have group function you (in most cases) have to use GROUP BY.....

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Posts
    416
    Ok the problem was I used column alias ( Departments) instead of real column name in group by clause

    Thanks


  8. #8
    Join Date
    Jan 2001
    Posts
    4
    You can use GROUP BY and HAVING in the same query, but not GROUP BY and WHERE.
    Try with a view.
    Valerie

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    thx guys!

    I was confused. Its with GROUP BY and ORDER BY clauses. This is what, I meant and got confused with GROUP BY and HAVING... Silly!

    ------------------------------------------------------------------------------------------------
    If you specify a group_by_clause in the same statement, this order_by_clause is restricted to the following expressions:

    Constants

    Aggregate functions

    Analytic functions

    The functions USER, UID, and SYSDATE

    Expressions identical to those in the group_by_clause

    Expressions involving the above expressions that evaluate to the same value for all rows in a group.

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    -------------------
    vgeraux :
    You can use GROUP BY and HAVING in the same query, but not GROUP BY and WHERE.
    Try with a view.
    ---------------------

    Also incorrect. You can use a WHERE with a GROUP BY and a HAVING. This is not a problem. The WHERE restricts the original records that are subsequently grouped using the GROUP BY, creating a new recordset. This recordset is then restricted by the HAVING clause.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width