SQL Server Performance

MAX(AVG(SAL)) query

Discussion in 'Getting Started' started by RVG, Jan 15, 2010.

  1. RVG Member

    Hi,
    When I tried to execute the below given query it is getting executed in Oracle but it is not getting executed in SQL or MYSQL. Can any one help me in solving this query.
    Table is the basic(predefined schema) employee table in Oracle.
    Query:
    select max(avg(sal)) from employee group by deptno; //executes in Oracle//
    but when I write the same above query using max(avg(sal)) this function gives an error in MYSQL
    Thanks and Regards,
    Ravi Varma G
  2. preethi Member

    Hi Ravi,
    I have not tested this in MySQL but in MSSQL this is how it should be:
    SELECT MAX(avgsal) FROM (
    SELECT AVG(sal)) AS avgsal FROM employee GROUP BY deptno
    ) AS query1
    Hope this helps.
  3. RVG Member

    Hi preethi,
    I tried to execute this query but it is not working. anyways thanks for your input.
    Thanks & Regards,
    Ravi Varma.
  4. preethi Member

    Are you getting any errors?
  5. RVG Member

    yeah I am getting errors. when I tried to execute this query...
  6. Madhivanan Moderator

    [quote user="RVG"]yeah I am getting errors. when I tried to execute this query...[/quote]
    No one can understand if you say just "errors" [;)]
    Have you tried the following?
    SELECT MAX(avgsal) FROM (
    SELECT AVG(sal) AS avgsal FROM employee GROUP BY deptno
    ) AS query1
  7. RVG Member

    Hello Mr.Madhivanan,
    I tried to run your query it is getting executed. I really thank you.
    Thanks & Regards,
    Ravi Varma G
  8. Madhivanan Moderator

    [quote user="RVG"]
    Hello Mr.Madhivanan,
    I tried to run your query it is getting executed. I really thank you.
    Thanks & Regards,
    Ravi Varma G
    [/quote]
    You are welcome [:)]
  9. preethi Member

    Didn't you say that you were getting errors for the same query? Am I missing something here?
  10. Madhivanan Moderator

    [quote user="preethi"]
    Didn't you say that you were getting errors for the same query? Am I missing something here?
    [/quote]
    Your query has an extra brace after SAL [:)]
  11. preethi Member

  12. RVG Member

    Hi Mr.Preethiviraj,I Tried your query response and It got executed thanks for your response.Thanks & Regards,Ravi Varma
  13. preethi Member

    You are welcome. I made a mistake in the code anyway.
  14. Adriaan New Member

    Perhaps this ...
    select top 1 avg(sal) from employee group by deptno order by avg(sal) desc
  15. Madhivanan Moderator

    [quote user="Adriaan"]
    Perhaps this ...
    select top 1 avg(sal) from employee group by deptno order by avg(sal) desc
    [/quote]
    OP specified that he/she uses Mysql so top wont work
    It should be
    select avg(sal) from employee group by deptno order by avg(sal) desc limit 1;
  16. RVG Member

    Hello Mr.Adrian,
    I tried to run your query it is getting executed. I really thank you.
    Thanks & Regards,
    Ravi Varma G

Share This Page