Derived table help - SQL Server Forums

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)

Derived table help

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 13:38:12

I am trying to select an average of numbers and compare that to the actual numbers. This is exactly what I am trying to do:

select the name of the country and the life expectancy
for each country that has a life expectancy less than the
average life expectancy.

I have this so far

Select *
from(
Select distinct avg(C.LifeExpectancy) as AverageLifeExpectancy
      ,C.Continent
      ,C.LifeExpectancy as life
from Country C
group by C.LifeExpectancy desc
)t
Where t.AverageLifeExpectancy < t.life;

This works but doesn't select anything. If I change the t.life to a number like '100' it works perfect. what am I doing wrong?

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-09 : 14:41:38

quote:
Originally posted by jgonzalez14

I am trying to select an average of numbers and compare that to the actual numbers. This is exactly what I am trying to do:

select the name of the country and the life expectancy
for each country that has a life expectancy less than the
average life expectancy.

I have this so far

Select t.Continent,t.AverageLifeExpectancy,S.LifeExpectancy as life
from(
Select avg(LifeExpectancy) as AverageLifeExpectancy
,Continent
from Country
group by Continent
)t
inner join ( select distinct LifeExpectancy,Continent
from Country)S
on t.Continent = S.Continent
Where S.lifeExpectancy < t.AverageLifeExpectancy ;

This works but doesn't select anything. If I change the t.life to a number like '100' it works perfect. what am I doing wrong?

Thanks

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 20:07:48

This works but is not exactly what i need to have returned. I need to find the average of all countries (which is currently 66.48) and compare it to each country and find out which one is less then it. I not sure how to do this

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 20:15:48

Something like this
Select C.LifeExpectancy as average
from (select avg(LifeExpectancy) as average
from Country
)t
Country C
where C.LifeExpectancy < t.average

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-09 : 20:36:18

quote:
Originally posted by sodeep

quote:
Originally posted by jgonzalez14

I am trying to select an average of numbers and compare that to the actual numbers. This is exactly what I am trying to do:

select the name of the country and the life expectancy
for each country that has a life expectancy less than the
average life expectancy.

I have this so far

Select S.Continent,t.AverageLifeExpectancy,S.LifeExpectancy as life
from(
Select avg(LifeExpectancy) as AverageLifeExpectancy
from Country
)t
cross join ( select distinct LifeExpectancy,Continent
from Country)S
Where S.lifeExpectancy < t.AverageLifeExpectancy ;

This works but doesn't select anything. If I change the t.life to a number like '100' it works perfect. what am I doing wrong?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 23:19:09

is this what you want?

Select *
from(
Select distinct avg(C.LifeExpectancy) over (partition by Continent)as AverageLifeExpectancy,
avg(C.LifeExpectancy) over () as TotalAverageLifeExpectancy
      ,C.Continent
      ,C.LifeExpectancy as life
from Country C
group by C.LifeExpectancy desc
)t
Where t.AverageLifeExpectancy<t.TotalAverageLifeExpectancy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources