In Databricks, you can round numbers to 2 decimal places using the following methods:

1. SQL round Function:

This is the most direct way to round numbers in Databricks SQL or when working with DataFrames:

SELECT round(your_column, 2) AS rounded_column
FROM your_table

• your_column: The column containing the numeric values you want to round.
• 2: The number of decimal places to round to.

Important Considerations for round:

• Rounding Mode: Databricks’ round function uses HALF_UP rounding by default, where values of .5 and above are rounded up.
• Alternative: If you need HALF_EVEN rounding (where .5 is rounded to the nearest even number), use the bround function.

2. PySpark round Function:

When working with PySpark DataFrames, you can use the round function within a withColumn operation:

from pyspark.sql.functions import round

df = df.withColumn("rounded_column", round(df["your_column"], 2))

3. Python’s Built-in round Function:

If you’re manipulating individual numbers in Python code within Databricks, use the standard Python round function:

number = 3.14159
rounded_number = round(number, 2)  # Output: 3.14

4. Formatting for Display:

If you just want to display numbers with two decimal places without changing their underlying value, use formatting:

• SQL: format_number(your_column, 2)
• PySpark: format_number(col("your_column"), 2)
• Python: "{:.2f}".format(your_number)

Example:

Let’s say you have a DataFrame df with a column called value:

+-------+
| value |
+-------+
| 12.345|
|  6.789|
|  0.123|
+-------+

Applying the round function in SQL:

SELECT round(value, 2) AS rounded_value
FROM df

Output:

+-------------+
| rounded_value|
+-------------+
|       12.35|
|        6.79|
|        0.12|
+-------------+

Additional Tips:

• Handling Nulls: Be sure to handle null values appropriately. The round function will return null if the input is null.
• Data Types: Be mindful of the data types you’re working with. The round function expects numeric input.