Select query with specific criteria

[srinivasc_it]

Hi,

Problem statement:-
I have a "students" table with 3 columns: Name, Class, Marks. With following data:-

Name Class Marks
Paul A 85
Peter B 70
Jhon A 75
Alba B 80
Mary A 95
Lucy B 75

I need to "display the Names of all students in each class(A,B) whose marks are greater than the average marks of that class " I.e. If The Average marks of class A = 86, and of class B= 76

Then it should display names of "Mary A" and "Alba B" .

My partial solution:-

To get the Average marks of each class one can write the following query:

SELECT AVG(marks) FROM Students Where Class='A'
SELECT AVG(marks) FROM Students Where Class='B'

Query to get the names of the students whose marks are above average:

SELECT Names FROM Students WHERE marks > (SELECT AVG(marks) FROM Students Where Class='A')

But, the above query only returns Names from single "class". How to modify the above query that displays names of students from multiple classes two or more?


Thanks in Advance

[Slope]

Unfortunately, I don’t have time to mockup test data and play with this right now, but I think something like this should give you what you are looking for.

SELECT s.Names
FROM Students s
JOIN
(
SELECT Class, AVG(marks) AS ClassAvg
FROM Students
GROUP BY Class
) ca ON ca.Class = s.Class
WHERE s.Marks >= ca.ClassAvg