Join using AVG value - 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)

Join using AVG value

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-16 : 06:12:23

I am trying to show average values, then join the returned average values to a second table that shows a description for the average value.

My first problem is that I can't work out how to join on the average value. Can anyone help with this?
The error I get is:


An aggregate cannot appear in an ON clause unless it is in a 
subquery contained in a HAVING clause or select list, and the 
column being aggregated is an outer reference.

Secondly, the average values are calculated using a column of type INT. Will the average values automatically be calculated as INT's, or will there be a decimal place. If there is a decimal place, how do I round to the nearest INT so that the JOIN works correctly? My query is:


SELECT
	AVG(t.overallResultID) as [average],
	t.[name],
	t.surname,
	t.pdruiID,
	ior.overallResult
FROM
	@tmp t INNER JOIN
	[tbl_OverallResults] ior ON ior.ID = AVG(t.overallResultID) -- error here!
GROUP BY
	t.pdruiID, t.surname, t.[name]

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 07:50:28

Try this:

select dt.*, ior.overallResult
from
(
 SELECT
 	AVG(t.overallResultID) as [average],
 	t.[name],
 	t.surname,
 	t.pdruiID
 FROM
 	@tmp t
 GROUP BY
 	t.pdruiID, t.surname, t.[name]
)dt
join [tbl_OverallResults] ior 
  ON ior.ID = dt.[average]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 07:53:47

quote:
Secondly, the average values are calculated using a column of type INT. Will the average values automatically be calculated as INT's, or will there be a decimal place.


select avg(val) 
from
(select 1 as val union all
 select 8)dt

gives 4 instead of 4.5

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-16 : 08:20:30

You need to multiply by 1.0 for accuracy

select avg(val*1.0)
from
(select 1 as val union all
select 8)dt

Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-16 : 11:24:54

Thank you all for your valuable input. webfred your example works great.

quote:
Originally posted by webfred

select avg(val)
from
(select 1 as val union all
select 8)dt

gives 4 instead of 4.5

This is interesting, as mathematically I would expect anything ending in .5 to round upwards (e.g. 4.5 to become 5).
I've used a slightly modified version using ROUND to get the (presumably!) correct result...


select round(avg(val * 1.0), 0)
from
(select 1 as val union all
 select 8 union all
 select 1)dt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 11:44:48

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources