The order in which you ROUND matters in SQL

Photo by Chaitanya Tatikonda on Unsplash

The order in which you ROUND matters in SQL

Constantin Lungu's photo
Constantin Lungu
·

1 min read

Rounding numbers in SQL is one of the simplest operation, but it's important to we pay attention to how we apply it.

1) Consider the sequence in which you apply rounding and aggregation functions.

When you ROUND a value and then aggregate it using functions like SUM or AVG, the outcome may differ significantly compared to first aggregating the values and then rounding the result.

No alt text provided for this image

As with all things, take into consideration your context and business problem you're trying to solve.

2) Be sure to use the proper rounding function for the job:
- ROUND - nearest integer or decimal place (if specified) 1.4 =1 but 1.5 =>2
- FLOOR - largest integer that is not greater than our value 1.7 => 1
- CEIL/CEILING - smallest integer than is not smaller than our value 1.4 => 2

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

bigquerySQLDatabasesgoogle cloud