|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
April 27th, 2004, 12:36 AM
|
|||
|
|||
|
Finding values greater than grouped averages
Might be a bit hard to get your head around, but basically, using SQL-99 I have to find birds that are larger than the average height in that family of birds. I'm currently using a subquery that groups all the averages, but I don't know how to then find out what is higher in each family.
select Height, BCommonName from Birds where Height > ( select avg(Height) from Birds where FCode = Birds.FCode group by FCode ) This returns nothing. PLEASE HELP ME.
|
|
#2
April 27th, 2004, 12:48 AM
|
|||
|
|||
|
I see some errors in your attempt...can you post the table and relevant fields? I have an idea about 'em from your query, but not totally.
Last edited by Username=NULL : April 27th, 2004 at 01:10 AM.
|
|
#3
April 27th, 2004, 01:16 AM
|
|||
|
|||
|
just gonna take a stab...is this what your table looks like??
Birds ----- BCommonName //name of the bird? Height FCode //family code each bird belongs to? give this a shot... Code:
select BCommonName, height
from Birds B
join (select distinct FCode, AVG(height) as avgHeight
from Birds
group by FCode) as avgQuery
on B.FCode = avgQuery.Fcode
where B.height > avgQuery.avgHeight
|
|
#4
April 27th, 2004, 03:05 AM
|
|||
|
|||
|
Code:
select Height, BCommonName from Birds b where Height > ( select avg(Height) from Birds where FCode = B.FCode)
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Finding values greater than grouped averages |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
