Converting numbers

Question

Post reply

Converting numbers

Mark-459099

SSC Eights!

Points: 911
More actions
June 27, 2008 at 2:58 pm

#193266

how do you convert integers to decimals or floats in order to calculate percentages.
I tried this:
SELECT a.ClassId, s.race,
SUM(case when a.Status='P' then 1 else 0 end) AS Present, SUM(case when a.Status='A' then 1 else 0 end) AS Absent, CONVERT(DECIMAL, Absent/(Present + Absent)) AS PercentAbsent
But I got errors. Is my convert expression correct?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan. T. Hall of Fame Points: 3221 More actions · Answer 1

The error you are getting is because you are trying to reference the alias instead of the data itself.

For example:

DECLARE @a int

DECLARE @b-2 int

SET @a = 1

SET @b-2 = 3

SELECT CONVERT(DECIMAL(12,3), @a)/@b AS PercentAbsent

will give you the percentage.

However, I think you should look at you query and group the data because I think you want something like

class

, race

, (SELECT COUNT(id) FROM table WHERE absent) / (SELECT COUNT(id) FROM table)

but that is speculation:)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Please supply more data. Refer to link in my signature. Supply complete error statement including error number, state and text and then maybe we can help

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Mark (6/27/2008)
But I got errors.

Can't read your mind, Mark... please post the exact text from the errors...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)