1

I'm working in a Sybase database.Before I was trying to Round using SQL Round function but Some data mismatch problem I need to build a java function which returns a Round value of database outputs.

SQL Query :-

With Round :- select Country,Round(ISNULL(sum(value),0),1) as Round_Value from Database ## Result :- 15012.8

Without Round :- select Country,sum(value) as Without_Round from Database ## Result :- 15012.82906

Now when I'm trying to Round the database value(Without Round Value) using java function then getting the below result ...

Java :- 

  public static void main(String []args)  
    {
         double value=15012.82906;
         System.out.println("Test Value:"+ Math.round(value));##Result :- 15013

    }

Is there any way to get the same value from SQL database and Java round function ? Thanks in advance.

Biswajit Mahato
  • 23
  • 2
  • 9
  • Possible duplicate of [How to round a number to n decimal places in Java](http://stackoverflow.com/questions/153724/how-to-round-a-number-to-n-decimal-places-in-java) – Stefano Zanini Apr 05 '17 at 09:20
  • Actually it is not only focus of decimal round but also focus on SQL round which is getting the different result. So I think it is not duplicate. I tried the duplicate result but not getting the expected result . So can you please help me out ??? – Biswajit Mahato Apr 05 '17 at 09:52

2 Answers2

0

Your SQL queries are returning different results because first one rounds up to first decimal, and the second one doesn't. Java code is rounding without decimals instead.

You have to decide if you want the result to be rounded and, if so, how many decimals you want. Both Java and SQL can achieve whatever you decide.

With your code you've already covered SQL not rounded, SQL rounded with one decimal and Java rounded without decimals; here's the missing options:

SQL rounded without decimals

select Country, Round(sum(value), 0) as Round_Value
from   Database

Java rounded with decimals

This SO answer

Community
  • 1
  • 1
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
0

MySql uses the Half Up rounding in round function. If you are using DecimalFormat in Java then set rounding mode to HALF_UP . It will give same result as Mysql .

DecimalFormat decimalFormatter = new DecimalFormat("#.##"); decimalFormatter.setRoundingMode(RoundingMode.HALF_UP);

Select ROUND(12.38185000,2)

returns 12.38 in MYSQL

decimalFormatter.format(12.38185000);

Prints 12.39 if rounding mode is not set or set to UP

Tushar Wasson
  • 494
  • 5
  • 10