cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Rounding in the databricks SQL editor

Hubert-Dudek
Esteemed Contributor III

In SQL editor (inside SQL experience), executing expressions 1 โ€“ 0.999 gives the result 0.

image.png

1 ACCEPTED SOLUTION

Accepted Solutions

Hubert-Dudek
Esteemed Contributor III

The incorrect result is because of default rounding to format 0.00. We can implement the below solution to solve the problem. It must be applied to every individual query.

Option 1: Edit Visualisation

After clicking Edit visualization, we can change the default Number format to 0.000

image.pngOption 2: Format number

We can use the format_number function to override the default rounding format.image.pngGeneral solution needed

Setting the default format for numbers in SQL Editor should be possible. Currently, when we go to โ€œSQL Admin Consoleโ€, there is only the possibility to change the default date & time format.

@Lindsay Olsonโ€‹ @Prabakar Ammeappinโ€‹ @Werner Stinckensโ€‹ @Jose Gonzalezโ€‹ , please pass it on as feedback if possible.

image.png

View solution in original post

4 REPLIES 4

Hubert-Dudek
Esteemed Contributor III

The incorrect result is because of default rounding to format 0.00. We can implement the below solution to solve the problem. It must be applied to every individual query.

Option 1: Edit Visualisation

After clicking Edit visualization, we can change the default Number format to 0.000

image.pngOption 2: Format number

We can use the format_number function to override the default rounding format.image.pngGeneral solution needed

Setting the default format for numbers in SQL Editor should be possible. Currently, when we go to โ€œSQL Admin Consoleโ€, there is only the possibility to change the default date & time format.

@Lindsay Olsonโ€‹ @Prabakar Ammeappinโ€‹ @Werner Stinckensโ€‹ @Jose Gonzalezโ€‹ , please pass it on as feedback if possible.

image.png

Prabakar
Databricks Employee
Databricks Employee
Sure @Hubert Dudek we'll test this internally and will take it to the team.

Hubert-Dudek
Esteemed Contributor III

Thank you @Prabakar Ammeappinโ€‹ 

-werners-
Esteemed Contributor III

Nice find

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local communityโ€”sign up today to get started!

Sign Up Now