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

    ,l.[Total Leads]
    ,SUM(s.SalesCount) AS [New Clients]
    ,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.

  • \$\begingroup\$ I'm assuming that SalesCount and TotalSales are integers? \$\endgroup\$
    – 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])) + '%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.