0

Recently, I came across an anomaly that while dividing two integers, I am getting only the quotients and reminders are simply ignored.

SELECT 12/8,12%8

The above query gives 1 and 4 respectively, which is similar to Java/C programming. Again applying Java/C programming methods used below calculations to obtain the expected value.

SELECT 12.0/8,12/8.0

The answer is 1.5000 and 1.5000 respectively. Working on my task I got a situation to obtain percentage value across two counted values (integers) and I stuck up with the results similar to the former query. Then I worked out through the same by multiplying one of the value with 1.0 . This solved my issue.

But later on, going through few scripts, used in my project (developed long back), I noticed in certain cases the decimal values are returned from the query even though two counted values (whole numbers) are divided.

I first noticed this in Netezza. But same holds true in SQL Server as well.

Please advise on what basis the datatypes of returned values are decided.

Gdek
  • 81
  • 4
  • 11
  • Sorry, but I don't get it, what do you expect toget from the database? – CiucaS Sep 12 '14 at 06:59
  • So, ideally when two whole numbers are divided it should return a whole number rather than a decimal number . But, I have got a decimal value as result while calculating percentage of two values(these vales are counted numbers. Thus int/int = decimal) – Gdek Sep 12 '14 at 12:56
  • So you want 12/8 to return 1? – CiucaS Sep 13 '14 at 00:41
  • Yes @CiucaS, because we are dividing an integer by another integer. – Gdek Sep 15 '14 at 14:44

1 Answers1

1

When dividing both integers, it will perform integer division, which returns an integer. To perform floating point division, you must either cast one or both of the operands to float/decimal/double.

SELECT cast(12 as float)/8
SELECT 12/cast(8 as float)
SELECT cast(12 as float)/cast(8 as float)
SELECT cast(12/8 as float)

Note that the last query is different since the integer division is performed first before casting to float,that is why the decimal value was already lost.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • Yes You're right. This is how the DB calculates. So, ideally when two whole numbers are divided it should return a whole number rather than a decimal number . But, I have got a decimal value as result while calculating percentage of two values(these vales are counted numbers. Thus int/int = decimal) – Gdek Sep 12 '14 at 12:46