AVG Function

[hc1619]

I have a photo rating system on my site.. one table, RateMe, which contains the votes, who made the vote, and who the vote was for.

In my stored proc, whenever a vote is cast I then do a count of the votes and get a new average to store against the members profile. This is what I'm doing at the moment:

Update Members
Set Votes = (SELECT COunt(RateID)
From RateMe
Where rMemberID = @rMemberID),rating = (SELECT AVG(CAST(rating as decimal(5,4)))
From RateMe
Where rMemberID = @rMemberID)
Where MemberID = @rmemberID

But I can't get the average to come out with decimal points.. ie, 5.67. All i get is 5, or 6, just a single number. Is there something wrong I am missing here?

 

[ESquared]

You could do the average yourself:

SELECT 1.0 * Sum(rating) / Count(rating)

or just try the 1.0 trick by itself

SELECT Avg(1.0 * rating)

 

[hc1619]

like this?

Update Members
Set Votes =
(SELECT COunt(RateID) From RateMe Where rMemberID = @rMemberID),
,rating =
(SELECT Avg(1.0 * rating) from RateMe where rMemberID = @rMemberID)
From RateMe
Where MemberID = @rmemberID

 

[hc1619]

... cos its bringing up error: "Incorrect syntax near 'from'

 

[hc1619]

ignore last post.. dumb syntax error that i missed.

OK i got the code working.. but whats being stored in my Members table is still just a single digit where the final AVG should be.. its called "Rating" and is set to tinyint.

Also.. when I test it and make a vote of 10 it throws an arithmetic overflow error? Why would this be??

 

[ESquared]

Why would you use tinyint? It seems an entirely unsuitable data type for something such as an average. Try decimal.

 

[hc1619]

whoops.. i saw the precision thing in EM on the tinyint field and thought it could handle it.. didnt realise it was greyed out tho.

OK so i have changed it to decimal.. still storing just the single digit. What else could I be missing?

 

[Catadmin]

What is your precision and scale on your decimal value?

Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[hc1619]

precision 3, scale 0

 

[ESquared]

which is 3 digits, 0 to the right of the decimal point.

try 20,10 and see what you get.

 

[Catadmin]

That's your problem. Scale is the # of digits to the right of the decimal. You have it set at 0 which means you only want whole number decimals.

Precision is the TOTAL # of digits both left or right of the decimal, so you've currently got your decimal set up as XXX. with nothing after.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[hc1619]

thank you!! never dealt with decimal or precision/scale stuff.. had no idea. thanks

 

[ESquared]

Do you know about SQL Server Books Online? It's the help file installed automatically with SQL Server and Enterprise Manager. When you have a question, you should start with some research in there. All you need to know about decimal data types is in it.