-3

I declared a variable @avgMis as a decimal(2,2) in SQL Server. Then I tried to divide two integers, expecting a decimal value. Instead my answer is 0.00.

How do I divide @avgMis = (1/(1+2+1)) to get 0.25 in SQL Server?

Johnny
  • 819
  • 1
  • 10
  • 24
  • 2
    `select 1.0/(1+2+1);` or `select 1/(1.0+2+1);` – Luuk Apr 22 '22 at 19:27
  • The numerators and denominators in your division were integers. when we divide integer 1 by integer 4 result is 0, which in your case was stored in @avgMis so became 0.00 To avoid integer division add multiplication with say 1.00 [demo](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ba8cdac9998684fbd13cfd0e37f8d1b5) – DhruvJoshi Apr 22 '22 at 19:39
  • Is it not possible for your result to be 1 or is the numerator always less than the denominator? – shawnt00 Apr 22 '22 at 19:52
  • More often than not, you're not the first person to come across an issue, and can often solve the problem faster by viewing existing answers than waiting for new ones. See [this search](https://stackoverflow.com/search?q=divide+two+integers+sql-server&searchOn=3), for example. – Aaron Bertrand Apr 22 '22 at 20:12

1 Answers1

0

cast as decimal

DECLARE @x INT = 10, @y INT = 3, @z decimal(6,2)
SELECT @z = CAST(@x AS decimal(8,4))/CAST(@y AS DECIMAL(8,4))
SELECT @z

result would be 3.33

in your situation

SELECT CAST( CAST(1 AS DECIMAL(6,2))/CAST((1+2+1) AS DECIMAL(6,2)) AS DECIMAL(6,2)) AS result

enter image description here

Power Mouse
  • 727
  • 6
  • 16
  • I also found that by using actual decimals in my input values, the return result is also a decimal, i.e., select 1.0/(1+2+1); or select 1/(1.0+2+1); – Johnny Apr 28 '22 at 12:09