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