The order in which you ROUND matters in SQL

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.

WITH input_data AS (

  SELECT 1.2 AS quantity, 'UK' AS country
  UNION ALL
  SELECT 1.4 AS quantity, 'UK' AS country
  UNION ALL
  SELECT 2.2 AS quantity, 'UK' AS country
  UNION ALL
  SELECT 1.8 AS quantity, 'US' AS country
  UNION ALL
  SELECT 1.5 AS quantity, 'US' AS country
  UNION ALL
  SELECT 2.6 AS quantity, 'US' AS country
)

SELECT
  country,
  SUM(ROUND(quantity,0)) AS sum_rounded_quantities, -- rounds, them sums
  ROUND(SUM(quantity),0) AS round_sum_of_quantities -- sums, then rounds

FROM input_data

GROUP BY country

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