Search code examples
sqlgoogle-bigquerydecimal

How to reduce decimal places to just 2?

I have this query in BigQuery

SELECT Id, 
       AVG(VeryActiveMinutes) AS avg_very_active, 
       SUM(VeryActiveMinutes) AS total_very_active,
       SUM(SedentaryMinutes) AS total_sedentary
FROM `fit-mantra-410702.fitbit_data.dailyactivity`
GROUP BY Id
ORDER BY avg_very_active DESC

The avg_very_active is returning values such as this 87.333333333333329.

I have tried to use CAST() in and outside of the AVG() to reduce to only 2 decimal places and no luck. How to I fix this?

Solution

  • Use ROUND(...,2) to return the aggregated result to 2 decimal places.

    SELECT Id, 
  ROUND(AVG(VeryActiveMinutes),2) AS avg_very_active, 
  SUM(VeryActiveMinutes) AS total_very_active,
  SUM(SedentaryMinutes) AS total_sedentary
FROM `fit-mantra-410702.fitbit_data.dailyactivity`
GROUP BY Id
ORDER BY avg_very_active DESC