Finding the average of a column

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

General SQL Server Forums

New to SQL Server Programming

Finding the average of a column

Author

Topic

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-17 : 12:48:27

Here is my requirement...

For each department that has one or more majors with a GPA under 1.0, print the name of the department and the average GPA of its majors.

Here is what I have done so far...

select major.dname, avg(student.gpa) as "AvgGPA" from student, major
where student.sid = major.sid and student.gpa < 1.0
group by major.dname

My problem is this...

I do want to find the department names that have one or more majors with a GPA under 1.0, however I want to show said Department Names average GPA of all the students, not just the average of their GPAs < 1.0, however I do want to show only those departments that posses a GPA Students with < 1.0 GPA.

Hopefully that is as clear.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-17 : 12:51:20

If I read your requirement correctly, take a look at the HAVING clause.
http://msdn.microsoft.com/en-us/library/ms180199.aspx

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-10-17 : 13:04:35

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-17 : 18:56:04

quote:
Originally posted by Lamprey

If I read your requirement correctly, take a look at the HAVING clause.
http://msdn.microsoft.com/en-us/library/ms180199.aspx

select major.dname, avg(student.gpa) as AvgGPA
from student, major
Where student.sid=major.sid
Group by major.dname having min(student.gpa) < 1.0

This found what I wanted. Thanks very much.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-17 : 19:10:36

This might be slightly more perfomant since the "exists" will only need to find the first occurance of gpa < 1.0 instead of finding the actual minimum:[CODE];with SandM
as (
select m.dname, s.gpa
from
student s
inner join
major m
on s.sid = m.sid
)
select sm.dname, avg(sm.gpa) AvgGPA
from SandM sm
where
exists (select *
from SandM sm1
where sm1.gpa < 1.0
and sm.dname = sm1.dname
)
group by
sm.dname[/CODE]But then again it might not...

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources