Link to home
Start Free TrialLog in
Avatar of shelbyinfotech
shelbyinfotech

asked on

how to calculate percentage in SQL Server without divide by zero error

How can I calculate a percentage in SQL server without getting a divide by zero error when my data contains legitimate zeros. I would like to do this in a UDF
Avatar of Limbeck
Limbeck

Avatar of shelbyinfotech

ASKER

Thanks; but I cannot use try/catch here as I am using SQL 2000.
Avatar of chapmandew
here is the logic....function following.


declare @i int, @j int
select @i = 5, @j = 0

select case when @j = 0 then 0 else @i/@j end
create function udf_divide
(
@i int,
@j int
)
returns int
as
begin
declare @k int

set @k = case when @j = 0 or @i = 0 then 0 else @i/@j end
return(@k)
end
Having to do this in SQL 2K5 and try remember that I have not used any 2K5 specific syntax, so appology if it did. The return result in a divide by zero case I have set as null, but potentially you want something else.

CREATE FUNCTION divide
(
	@a real,
	@b real
)
RETURNS real
AS
BEGIN
	DECLARE @result real
	SELECT @result = (case when @b = 0 then null else @a/@b end) 
	RETURN @result
END

Open in new window

CREATE FUNCTION Calc_Perc(@Value1 decimal(12,2), @Value2 decimal(12,2))
RETURNS decimal(12,2)
AS
BEGIN
DECLARE @Result decimal(12,2)

SET @Result = CASE WHEN @Value2 = 0 THEN 0 ELSE (@Value1 / @Value2) * 100 END

RETURN @Result
END
Avoid int, it types the return as having no decimal places.
thats fine...use my same code, but change the data types to decimal
Ok Chapmandew; I follow the logic on how to avoid the divide by zero error, however where Otana expanded oh this as to how i would calculate the percentage, it leaves a flawed result in the case of a Zero as Zero times anything is Zero, and would not show a 100% change
what result would you want in that example?
I would like to show 100% in either direction  in the case of a zero on either side, and a zero for the same values
so, if they are both zero then 0%, but if only 1 then 100%?
yes
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
right on the money ...thanks
Spoke too soon..my results are integer, should be decimal
instead of multiplying by 100, try 100.00