The order in which you ROUND matters in SQL
Updated
•1 min read
C
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
Rounding numbers in SQL is one of the simplest operation, but it's important to we pay attention to how we apply it.
- 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.
As with all things, take into consideration your context and business problem you're trying to solve.
- 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.
