CODEWITHSUNDEEP
sql-server
user5974397
user5974397

Reputation:

Subquery and aggregate functions

id  name    gpa  batch
1   jhon    2    y1
2   sandy   2.8  y2
4   kile    3    y2
5   siya    3.2  y1

I want to find the batch which got the maximum average gpa. so I wrote following code.

select max(gpa)
from student
where gpa in (select batch,AVG(gpa)
              from student
              group by batch)

I check the sub-query by executing it separately and it works fine.But there is an error in the main query.

Upvotes: 1

Views: 44

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use TOP instead:

SELECT TOP 1 batch
FROM student
GROUP BY batch
ORDER BY AVG(gpa) DESC

DEMO

Using MAX:

SELECT *
FROM(
    SELECT batch, avg_gpa = AVG(gpa)
    FROM student
    GROUP BY batch
) s
WHERE avg_gpa = (
    SELECT MAX(avg_gpa)
    FROM (
        SELECT batch, avg_gpa = AVG(gpa)
        FROM #student
        GROUP BY batch
    ) t
)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24813

SELECT TOP 1 batch, avg_gpa = AVG(gpa)
FROM   student
GROUP BY batch
ORDER BY avg_gpa desc

Upvotes: 0

Aniket Ghodke
Aniket Ghodke

Reputation: 9

Your subquery returns 2 values and In function in main query is looking for 1 item. I would suggest remove batch from internal query.

Upvotes: 0

Related Questions