0

How to get exact decimal values on dividing numbers in SQL?

Example : Dividing 8 over 25 will return 0.32.

I will have to get the exact decimals.

goofyui
  • 3,362
  • 20
  • 72
  • 128
  • 1
    Not sure I understand. `SELECT 8.0 / 25.0;` returns `0.320000`; why is that not correct? You can't have different values for scale and precision in a single column; if not displaying trailing 0's is important then you should be handling that in your presentation layer. – Thom A Mar 11 '19 at 16:33
  • 8.0/25.0 otherwise it's doing integer math which rounds. the result of an int divided by an int is an int, not a decimal. Alternatively define each value as decimal with precision and scale. – xQbert Mar 11 '19 at 16:33
  • @Larnu , i have integers as 8 and 25 in the database. i just realized that, i will have to convert them to decimal to get the decimal value – goofyui Mar 11 '19 at 16:34
  • 1
    SELECT 8 / (25+0.0) – John Cappelletti Mar 11 '19 at 16:35
  • 1
    Correct. An expression containing **only** integers will return an integer. If you need to return a `decimal`, you need to implicitly/explicitly convert *at least* one of the values to a `decimal`. – Thom A Mar 11 '19 at 16:36
  • 1
    You can use CONVERT OR CAST one of the integer values. SELECT CAST(8 AS NUMERIC(18,6))/25, CONVERT(NUMERIC(18,6),8) / 25 – Zeki Gumus Mar 11 '19 at 16:39
  • Thanks.. if i need to have only 2 decimals ?? we just need only 2 decimal units. How to get the value only as .32 and not .32000 – goofyui Mar 11 '19 at 16:46
  • it can't always do so, for example 1.0/3.0 or 22.0 / 7.0 will truncate the recurring values to a finite number of places – Cato Mar 11 '19 at 16:54
  • select cast(100 - ( (CONVERT(DECIMAL(4,2),8)) / (CONVERT(DECIMAL(4,2),25))) as varchar(10)) + '%' As PercentageScore -- I am working on percentage concept. I will have to have only 2 digit units – goofyui Mar 11 '19 at 16:56
  • Thanks,, this is completed.. – goofyui Mar 11 '19 at 17:27
  • Decimal precision and scale (4,2) gives allows for 99.99 but no more. Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2 If possible I"d make sure the datatype of these fields in your database are of the correct datatype and not just "int" – xQbert Mar 11 '19 at 18:32

0 Answers0