Averaging in a view does not display digits to the right of the decimal point
I am in the process of converting some MS Access tables and queries to a MS SQL 2008 svr. Most of Access's queries will port over fine (with some slight mods to make them work in SQL). I have a query in Access that creates a column that is an average over a group. The query works fine in Access giving me a result with the fraction (to the right of the decimal) displaying properly. However, when I created a similar View in SQL, I get only the Integer portion of the average result and just zeros to the right. Access Example Result ProjID ShortDescription WebDisplayProjectID ScoringAverage 11001 alskdfjl;asjflkal;s 11-001 9.23423 11002 asfl;kajs0klasjdfkl; 11-002 10.23456 SQL Example 11001 alskdfjl;asjflkal;s 11-001 9.00000 11002 asfl;kajs0klasjdfkl; 11-002 10.00000 MS Access Query SQL Code TRANSFORM Avg(tblMemberScoring.scorScoreAmount) AS TheValue SELECT tblMemberScoring.scorProjID, tblProjects.ShortDescription, Left([scorProjID],2) & "-" & Right([scorProjID],3) AS WebDisplayProjID FROM tblProjects INNER JOIN tblMemberScoring ON tblProjects.ID = tblMemberScoring.scorProjID GROUP BY tblMemberScoring.scorProjID, tblProjects.ShortDescription, Left([scorProjID],2) & "-" & Right([scorProjID],3) PIVOT "ScoringAverage"; MS SQL 2008 SQL code SELECT dbo.tblMemberScoring.scorProjID, dbo.tblProjects.ShortDescription, LEFT(dbo.tblMemberScoring.scorProjID, 2) + '-' + RIGHT(dbo.tblMemberScoring.scorProjID, 3) AS WebDisplayProjID, CAST(AVG(dbo.tblMemberScoring.scorScoreAmount) AS decimal(5, 3)) AS ScoringAverage FROM dbo.tblProjects INNER JOIN dbo.tblMemberScoring ON dbo.tblProjects.ID = dbo.tblMemberScoring.scorProjID GROUP BY dbo.tblMemberScoring.scorProjID, dbo.tblProjects.ShortDescription, LEFT(dbo.tblMemberScoring.scorProjID, 2) + '-' + RIGHT(dbo.tblMemberScoring.scorProjID, 3)
July 12th, 2011 1:21pm

That should work. What is the data type of the field in SQL? What do you get when you query the table directly like the query below, do the values retain the decimal parts or have the decimals been truncated? SELECT dbo.tblMemberScoring.scorScoreAmount FROM dbo.tblMemberScoringMartina White
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 1:48pm

Hi DrBlanston, According to your description, you would like to say you lost the decimal precision when you execute your sql statement in Sql Server. I have a question need you to confirm? Is it you execute your mentioned sql statement in SSMS, then get the below result: 11001 alskdfjl;asjflkal;s 11-001 9.00000 11002 asfl;kajs0klasjdfkl;11-002 10.00000 Note: decimal(5,3) means keep three decimal places after decimal point. Please check the two points below: 1. Execute the sql Statement below in SSMS to check whether the values of column scorScoreAmount are a series of decimal number with some non-zero digitals after decimal point. SELECT dbo.tblMemberScoring.scorScoreAmount FROM dbo.tblMemberScoring 2. Execute the sql Statement below in SSMS to check whether the result is a decimal number with some non-zero digitals after decimal point. SELECT AVG(dbo.tblMemberScoring.scorScoreAmount) FROM dbo.tblMemberScoring If both of the two points work fine, I recommend you to post this thread to our Transact-Sql forum: http://social.technet.microsoft.com/Forums/en-us/transactsql/threads , there are many experts focus on it. I hope this can help you, If you have anything unclear, please feel free to let me know. Thanks, Bill Lu
July 13th, 2011 9:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics