Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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

SELECT 
     u.User_FirstName
    ,l.[Total Leads]
    ,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.

share|improve this question
    
I'm assuming that SalesCount and TotalSales are integers? –  RubberDuck Jul 29 at 12:56

2 Answers 2

up vote 7 down vote accepted

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])
share|improve this answer

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])) + '%'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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