Trim a decimal to 2 places Bigquery

Question

I am currently running a query that runs a sum function and also divides this number. Currently I get values like 0.0904246741698848, and 1.6419814808335567. I want these decimals to be trimmed to 2 spaces past the decimal point. Their schema is a float. Here is my code. Thanks for the help.

#standardSQL
SELECT
  Serial,
  MAX(createdAt) AS Latest_Use,
  SUM(ConnectionTime/3600) as Total_Hours,
  COUNT(DISTINCT DeviceID) AS Devices_Connected
FROM `dataworks-356fa.FirebaseArchive.Firebase_ConnectionInfo`
WHERE PeripheralType = 1 or PeripheralType = 2 or PeripheralType = 12
GROUP BY Serial
ORDER BY Latest_Use DESC

user_1143681 · Answer

This will take a float in BigQuery and format it with two decimal points.

CAST(SUM(ConnectionTime/3600) AS STRING FORMAT '999,999.99')

Note: Add a a currency symbol (e.g., $) for currency ($999,999.99).

Example:

Trim a decimal to 2 places Bigquery

4 Answers