CODEWITHSUNDEEP
sqlsql-server
walangala
walangala

Reputation: 241

Divide two numeric columns in SQL and create new column with varchar result

I'm trying to divide two columns in SQL, I want to then have the results in a new column with a truncated result and by adding a decimal point. I'm at a loss here trying to get the syntax right.

Basically, column X and Y are numeric and I want the resulting value to be a percentage. 

     X/NULLIF (Y,0) * 100 AS PERCENTAGE

My result ends up as 

X.XXXXXXXXXXX

Ideally, I'm trying to get a result of 

 X.XX%

But my syntax of 

X/NULLIF(Y,0) * 100 + '$' AS PERCENTAGE

won't work since I can't convert type varchar to numeric. Also as to how to get a truncated result.

I tried casting, but that did not work either

Upvotes: 0

Views: 1628

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use str() or cast() to a decimal. For your purposes, you want a string, so str() is more appropriate:

SELECT str(X/NULLIF(Y, 0) * 100, 5, 2) + '%'

Note that this does left align the results, so you might consider:

SELECT cast(cast(100 * X/NULLIF(Y, 0) as decimal(5, 2)) as varchar(255)) + '%'

Upvotes: 1

Related Questions