Select query with specific criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query with specific criteria

Author

Topic

srinivasc_it
Starting Member

2 Posts

Posted - 2008-07-23 : 12:37:09

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 12:53:01

[code]SELECT s.*
FROM students s
INNER JOIN (SELECT Class,AVG(Marks*1.0) AS AvgMark
FROM students
GROUP BY Class)t
on t.Class=s.Class
WHERE s.Marks>t.AvgMark[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources