CODEWITHSUNDEEP
sql-servert-sql
Daniel
Daniel

Reputation: 11

How do I get divided 6 columns equal 100%?

I have a problem with dividing in SQL Server.

I am dividing 100/6.00. And it gives me 16.67, 16.67, 16.67, 16.67, 16.67, 16.67

Which is 100.02 at the end.

Is there a way to divide 100/6.00 and get 16.66, 16.66, 16.67, 16.67, 16.67, 16.67 as a result?

I don't look for solution like iif(final sum>100) then . Is there other way to do this?

Thanks

Upvotes: 1

Views: 311

Answers (3)

Nissus
Nissus

Reputation: 316

Can this somehow help you?

DECLARE @counter numeric(5,2) 
DECLARE @row int
DECLARE @number numeric(2,1)
SET @counter= 100
SET @number = 6.0
SET @row = 1
while @counter != 0
BEGIN
    select @counter,@number,@row,@counter/@number
    set @counter = @counter - @counter/@number 
    set @number = @number - 1
    set @row = @row + 1
END

Upvotes: 0

lptr
lptr

Reputation: 6788

...test

declare @amount decimal(9,4) = 100;
declare @den decimal(9,4) = 6.00;
declare @dp tinyint = 2; --decimal points for rounding

select val as colid, 
round(@amount/ceiling(@den), @dp,1) +
case when val <= power(10, @dp) * ((@amount- ceiling(@den) * round(@amount/ceiling(@den), @dp,1))%ceiling(@den)) then power(10.0000000000, -@dp) 
else 0.00000 end

from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as v(val) --max denominator:10
where val <= ceiling(@den);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

If you want exact division, then the general answer here is to use an exact type, such as NUMERIC or DECIMAL. Consider:

WITH yourTable AS (
    SELECT CAST(100.0 AS NUMERIC(10, 2)) AS num
)

SELECT num / 6 AS output       -- output is 16.666666
FROM yourTable;

Upvotes: 1

Related Questions