CODEWITHSUNDEEP
sqlsql-server
user4441082
user4441082

Reputation: 371

Inner join VS some other method

I am using sql-server.I have a table looks like

StudentName   Class  score
Jim            a1     80
Ann            a1     83
Bill           a2     90

I want to select student whose score is above the average score in his/her class. Here is my code:

Select a.StudentName
From Table a
inner Join Table b
On a.class=b.class 
where a.score>(select avg(b.score) From b
group by class);

The inner join method looks odd to me.Is it correct please? Is there any better way to achieve that please?

Upvotes: 0

Views: 70

Answers (2)

Elliveny
Elliveny

Reputation: 2203

How about:

;with ClassAverages as (
  select Class, AVG(score) as AVGScore FROM Table GROUP BY Class
)
SELECT StudentName FROM Table student
INNER JOIN ClassAverages ON ClassAverages.Class=student.Class
WHERE student.score>ClassAverages.AVGScore

Upvotes: 0

D Stanley
D Stanley

Reputation: 152501

You're close, but you can limit your sub-query and take out the JOIN:

Select a.StudentName
From Table a
where a.score > (
    select avg(b.score) score 
    From Table b
    where b.class = a.class);

Upvotes: 2

Related Questions