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.
Author |
Topic |
afifimk
Starting Member
22 Posts |
Posted - 2012-11-27 : 19:40:10
|
I am trying to get the average results 6 fileds, with 2 decimal points. The results are coming back as whole numbers.Thanks select QInstructorID_1 Instructor, AVG(((QInstructor_A_1 + QInstructor_A_2 + QInstructor_A_3 + QInstructor_A_4 + QInstructor_A_5 + QInstructor_A_6)/6)) Average from dbo.CE_Report where YEAR(QCourseStartDate_1) = 2012 AND QInstructor_A_6 is not null GROUP BY QInstructorID_1 |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-27 : 19:46:20
|
An integer divided by an integer is an integer, so 4/3 = 1 , 3/2 = 1, 7/2 = 3, etcselect QInstructorID_1 Instructor, AVG(((QInstructor_A_1 + QInstructor_A_2 + QInstructor_A_3 + QInstructor_A_4 + QInstructor_A_5 + QInstructor_A_6)*1.0/6)) Average from dbo.CE_Reportwhere YEAR(QCourseStartDate_1) = 2012 AND QInstructor_A_6 is not null GROUP BY QInstructorID_1so that red bit will change everything to a decimalJimEveryday I learn something that somebody else already knew |
|
|
BobsDesk
Starting Member
11 Posts |
Posted - 2012-11-27 : 19:46:28
|
Divide by 6.0Robert '); drop table students;-- ? |
|
|
afifimk
Starting Member
22 Posts |
Posted - 2012-11-28 : 08:57:02
|
Thanks - works great. What if I want to format the answer to show two decimals? |
|
|
BobsDesk
Starting Member
11 Posts |
Posted - 2012-11-28 : 10:29:25
|
Formatting should be done at the application layer butConvert(Decimal(18,2), AVG((QInstructor_A_1 + QInstructor_A_2 + QInstructor_A_3 + QInstructor_A_4 + QInstructor_A_5 + QInstructor_A_6)/6.0))Robert '); drop table students;-- ? |
|
|
|
|
|