SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Average		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

afifimk
Starting Member

USA
19 Posts
Posted - 11/27/2012 :  19:40:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 11/27/2012 :  19:46:20  Show Profile  Reply with Quote
An integer divided by an integer is an integer, so 4/3 = 1 , 3/2 = 1, 7/2 = 3, etc

select 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_Report
where YEAR(QCourseStartDate_1) = 2012 AND QInstructor_A_6 is not null GROUP BY QInstructorID_1

so that red bit will change everything to a decimal

Jim

Everyday I learn something that somebody else already knew
Edited by - jimf on 11/27/2012 19:47:48
Go to Top of Page

BobsDesk
Starting Member

11 Posts
Posted - 11/27/2012 :  19:46:28  Show Profile  Reply with Quote
Divide by 6.0

Robert '); drop table students;-- ?
Go to Top of Page

afifimk
Starting Member

USA
19 Posts
Posted - 11/28/2012 :  08:57:02  Show Profile  Reply with Quote
Thanks - works great. What if I want to format the answer to show two decimals?
Edited by - afifimk on 11/28/2012 08:57:48
Go to Top of Page

BobsDesk
Starting Member

11 Posts
Posted - 11/28/2012 :  10:29:25  Show Profile  Reply with Quote
Formatting should be done at the application layer but
Convert(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;-- ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000