Average 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)

Average Value

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-05 : 10:46:41

Hello,

I have a table with the following fields:

[DocumentId], [DocumentRatingId] and [DocumentRating]

DocumentRating is allways 1, 2, 3, 4 or 5.

I need calculate the average from all DocumentRating values given a DocumentId.

Then I need to get an integer from it. For example:
1.2 becomes 1
1.7 becomes 2

How can I do this?

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-05 : 10:59:13

[code]Select Convert(int, avg(DocumentRating)) as AvgRating
From Table
Where DocumentID = <some-value>[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 11:00:09

"Then I need to get an integer from it"

Is this what you are looking for?


SELECT ROUND(1.4, 0), ROUND(1.5, 0), ROUND(1.6, 0)

Answers:


---- ---- ---- 
1.0  2.0  2.0

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-05 : 12:25:43

To calculate the average you want, you need to convert the value to a numeric data type first before applying the AVG function, and then round off the average to the nearest whole number.


select
	AverageDocumentRating =
		floor(avg(a.[DocumentRating]+.000)+.5),
from
	Mytable a
where
	a.[DocumentId] = @DocumentId

CODO ERGO SUM

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-05 : 13:00:16

Sorry,

but when I use floor does not it transform the number:
1.3 in 1.0
1.7 in 1.0

Shouldn't I use Round?

And should I not calculate the value and then use:

RETURN average

Thanks,
Miguel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-05 : 13:07:13

quote:
Originally posted by shapper

Sorry,

but when I use floor does not it transform the number:
1.3 in 1.0
1.7 in 1.0

Shouldn't I use Round?

And should I not calculate the value and then use:

RETURN average

Thanks,
Miguel

It's good that you did not even try the code before posting a followup question.

CODO ERGO SUM

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-05 : 13:12:26

Hi Michael,

Sorry, I was trying this one:

SELECT ROUND(CONVERT(INT, AVG(DocumentRating)), 0) AS AverageRating
FROM DocumentsRatings
WHERE DocumentId = @DocumentId

And then I looked at your code which I couldn't completely understand and that's the reason of my post.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 13:39:14

Since you convert to INT before ROUNDing, it is the convert to INT that produces the error!

SELECT ROUND(AVG(DocumentRating), 0) AS AverageRating
FROM DocumentsRatings
WHERE DocumentId = @DocumentId

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources