# SQL percentage calculation

I have created a query in which there is a column that calculates a percentage:

SELECT
u.User_FirstName
,SUM(s.SalesCount) AS [New Clients]
,c.Target
,CONVERT(VARCHAR, CONVERT(DECIMAL(11,0),ROUND((CONVERT(DECIMAL(11,2), SUM(s.SalesCount)) / l.[Total Leads]) * 100, 0))) + '%'


As you can see, that last column is my percentage column, which uses a lot of CONVERT. I was wondering if there is a simpler or faster way to get a two-digit percentage in SQL Server 2008.

-
I'm assuming that SalesCount and TotalSales are integers? – RubberDuck Jul 29 '14 at 12:56

I feel that rofl pretty well covered how this could be done differently, but I want to point out what I feel to be an issue.

You really shouldn't be formatting the decimal results as a string. Formatting should be done in whatever application consumes the data you're sending it. As a user, I would be pretty upset to receive a "percentage" that I couldn't do any mathematical analysis on without first splitting out the "%" character, dividing by 100, and then formatting it as a percentage natively.

Select CONVERT(decimal(5,0), 1.0 * SUM([SalesCount]) / [Total Leads])

-

You can use implicit numeric conversion to save you a lot of coding here. Specifically, if you use 100.0 instead of 100 you will do floating-point arithmetic automatically. Consider the following:

100.0 * SUM([SalesCount]) / [Total Leads]


This will produce a floating point result that is accurate (no integer division).

Now, if you want to display that as a rounded string with no decimal places, I would cast it to a Decimal(5,0), and then a varchar.

Select CONVERT(varchar, CONVERT(decimal(5,0), 100.0 * SUM([SalesCount]) / [Total Leads])) + '%'

-