CODEWITHSUNDEEP
sql-serverpercentagequery-performance
shA.t
shA.t

Reputation: 16968

Alternative query for calculating percentage

In SQL Server for calculating percentage I have a function like below:

CREATE FUNCTION [dbo].[fuGetPercentage] ( @part FLOAT, @total FLOAT )
RETURNS FLOAT 
AS
BEGIN   
    DECLARE @Result FLOAT = 0, @Cent FLOAT = 100;
    IF (isnull(@total, 0) != 0)
        SET @Result = isnull(@part, 0) * @Cent / @total;
    RETURN @Result
END

I wonder that is there any better alternative for that, with same checks and a better calculating percentage like below:

SELECT (CASE ISNULL(total, 0) 
            WHEN 0 THEN 0 
            ELSE ISNULL(part, 0) * 100 / total 
        END) as percentage

I want to use it directly after SELECT like above.

Upvotes: 1

Views: 193

Answers (2)

shA.t
shA.t

Reputation: 16968

I use @DmitrijKultasev answer but now, I found that it has two problems:

  1. Error on conversion because of the overflow of result of multiply.
  2. Performance problem; because of that math multiply and its conversion.

So I change it to this:

SELECT
    CASE 
        WHEN total <> 0 AND part <> 0 THEN -- This will return 0 for Null values
            part * 100 / total
        ELSE 
            0
    END AS percentage;

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

There is one issue with using functions such as ISNULL. The query will not use indexes in that case. If the beauty of the code isn't in the first place then you can do something like that:

SELECT
    CASE WHEN total * part <> 0 /* will check that both total and part are not null and != 0*/ 
           THEN part * 100 / total
         ELSE 0
    END AS percentage;

Upvotes: 3

Related Questions