Subject Re: Percentage Calculation?
Author Adam
Hi Dan,

Like Alan said, it is generally a frowned upon practice to be doing
formatting in the database server. I am sure you could cast it to a
varchar and format it, something like

Cast(Cast(Sum(Sales-Cost)/Sum(Sales) AS Numeric(5,2)) AS Varchar(20))
|| '%'

Still it is definately not the most elegant solution, and you would
have to decide on the server the largest varchar possible, then
transmit a varchar rather than a more efficient numeric data type, all
for the sake of one line of code at the client.

Also, for safety you might need to add a clause

having Sum(Sales) > 0

or some Case statement in the select to make sure you don't get a
division by zero exception.

You could alternatively handle both of these requirements by writing a
UDF that accepted Sum(Cost) and Sum(Sales) and returned a formatted
varchar and internally handled the division by zero case.

If it were me though, I would return the values unformatted.