How can I format a decimal (DOUBLE) number in a select statement?

Question

Hi,
I have the following `select` statement in ***Athena*** and I would like to set the number of digits right to the decimal period of the column value:
```
SELECT
,(CAST(column1 AS double) / column2) cvr
FROM
  my_table
```
For example, I current get the following result: **0.6453524**

I would like to get it as **0.645**

Answer

Hi, it depends on what specifically do you want, with a similar example
**0.6456524** and 3 decimals
* If you want to have a ceiling of the previous decimal value **: 0.646** To do this you can use a UDF or this mathematic expression `CEIL((CAST(column1 AS double) / column2)*1000)/1000 as cvr`
* if you want to have a floor of the previous decimal value **: 0.645** `FLOOR((CAST(column1 AS double) / column2)*1000)/1000 as cvr`
* if you want to numerically round the previous decimal value **: 0.646**. To do this you can use `ROUND((CAST(column1 AS double) / column2), 3) as cvr`
You can always refer to the Athena operations documentation [here](https://docs.aws.amazon.com/athena/latest/ug/presto-functions.html)

Answer

You can use [`ROUND(col, 3)`](https://prestodb.io/docs/0.217/functions/math.html#round) to round a number to three decimal points:

```
SELECT ROUND(CAST(column1 AS DOUBLE) / column2), 3) AS cvr
FROM my_table
```

Answer

Suggest to use the round function. See [here](https://prestodb.io/docs/0.172/functions/math.html)

How can I format a decimal (DOUBLE) number in a select statement?

Conteúdo relevante