1. Registered User
Join Date
Jan 2008
Posts
23

i need to round the average of some records to one of the following values:
1
1.5
2
2.5
3
3.5
4
4.5
5

for example if i have the records in the database like:
5 , 1.5 , 1.5 , 5 , 5

then the average is: 5 + 1.5 + 1.5 + 5 + 5 = 18 / 5 = 3.6
then 3.6 should be rounded to the closest value from the above values set so it should be rounded to : 3.5
is it possible with the ROUND function? if so how the formula will look like?

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
okay, suppose the average is 1.75

the "closest" values are both 1.5 and 2.0

which one would you like?

3. Registered User
Join Date
Jan 2008
Posts
23
good point, mmmmm, may be i'll choose the higher value : 2

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
okay, try this -- ROUND(AVG(...)/5,1)*5

5. Registered User
Join Date
Jan 2008
Posts
23
exactly what i need, this is solved, many thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•