t-sql represent a repeating decimal value as a percentage

Question

So I have two columns TotalBoxes, and BoxesTicked, my total is 33, and the number of boxes ticked is 2.

So 2/33 gives me .06 (repeating)

T-sql returns 0. How do I write the T-SQL to return .06 only.

DECLARE @TotalBoxes int
DECLARE @TotalTicked int

SET @TotalBoxes = 33
SET @TotalTicked = 2

PRINT @TotalTicked/@TotalBoxes
PRINT ROUND(@TotalTicked/@TotalBoxes, 4,2)
PRINT CAST(@TotalTicked/@TotalBoxes as DECIMAL(4,2))

TIA.

score 0 · Answer 1 · answered Jul 12 '18 at 11:05

Both of your variables are INT and their division is shown as an INT by SQL Server. If you could cast either of them to a data type with decimals (like DECIMAL or NUMERIC), then the division would have decimal numbers.

A caveat to cast an integer to a decimal is multiplying by one like 1.0 which is a decimal (note the decimal point).

DECLARE @TotalBoxes int
DECLARE @TotalTicked int

SET @TotalBoxes = 33
SET @TotalTicked = 2

PRINT @TotalTicked * 1.0 /@TotalBoxes
PRINT ROUND(@TotalTicked * 1.0/@TotalBoxes, 4,2)
PRINT CAST(@TotalTicked * 1.0/@TotalBoxes as DECIMAL(4,2))

Result:

0.060606060606
0.060600000000
0.06

t-sql represent a repeating decimal value as a percentage

1 Answers1