percent problem - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

percent problem

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2007-02-26 : 04:03:48

Hi
I have the following code but it's not giving the desired results when returning the %.The line in bold is the problem. It returns either 100.00 or .00. For example 41/78 returns .00 when it should be 52.56. Can anyone tell me what i've done wrong please?

SELECT
[Academic Year],
StudentDetailID,
[Student Ref No],
[Total Actual Attendance],
[Total Possible Attendance],
CAST ([Total Actual Attendance]/[Total Possible Attendance]*100 AS DECIMAL (19,2)) AS [Attendance Percent]

FROM

(SELECT TOP 100 PERCENT [Academic Year], StudentDetailID, [Student Ref No], SUM([Actual Attendance]) AS [Total Actual Attendance],
SUM([Possible Attendance]) AS [Total Possible Attendance]
FROM dbo.mvrRegisterStudent
GROUP BY [Academic Year], StudentDetailID, [Student Ref No]
HAVING (StudentDetailID IS NOT NULL) AND (SUM([Actual Attendance]) <> 0)
ORDER BY StudentDetailID) AS Total

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-02-26 : 04:33:22

If the [Total Actual Attendance] and [Total Possible Attendance] are integers, you have to cast them before dividing them.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-26 : 04:34:04

CAST (1.0 * [Total Actual Attendance]/[Total Possible Attendance]*100 AS DECIMAL (19,2)) AS [Attendance Percent]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 04:35:35

[code]
CAST ([Total Actual Attendance] * 100.0 / [Total Possible Attendance] *100 AS DECIMAL (19,2)) AS [Attendance Percent]
[/code]
[Total Actual Attendance] and [Total Possible Attendance] must be integer. Integer divide by integer will give you an integer value. By multiplying by 100.0, will convert the result to decimal

chipembele
Posting Yak Master

106 Posts

Posted - 2007-02-26 : 04:43:46

Thanks for your replies.

nr, that worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources