Can the result of the avg function retain up to 4 decimal places? How can it retain 6 decimal places? (Solved)

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: avg函数结果最多保留4位吗?怎么可以保留6位呢? （已解决）

| username: TiDBer_NpMjpo3g

To improve efficiency, please provide the following information. A clear problem description can help resolve the issue faster:

[Overview] Scenario + Problem Overview
I need to round the mean result to 3 decimal places using the round half to even rule. However, I found that the avg function result retains up to 4 decimal places, which affects my final rounding result.

[Application Framework and Development Adaptation Business Logic]

[Background] Operations performed

[Phenomenon] Business and database phenomena

[Problem] Current issue encountered

[Business Impact]

[TiDB Version]

[Attachments] Relevant logs and monitoring

2
| username: Billmay表妹 | Original post link

In TiDB, the default precision of the AVG function is to retain 4 decimal places. If you need to retain 6 decimal places, you can use the FORMAT function to format the result of the AVG function.

For example, if you want to calculate the average value of column b in table t and retain 6 decimal places, you can use the following SQL statement:

SELECT FORMAT(AVG(b), 6) FROM t;

Here, AVG(b) calculates the average value of column b, and the FORMAT function formats the result to retain 6 decimal places as a string.

3
| username: TiDBer_NpMjpo3g | Original post link

Hello, the avg function retains up to 4 decimal places, and the format function rounding to 6 decimal places just adds zeros. Is it impossible for the avg function to retain 6 decimal places?

4
| username: TiDBer_NpMjpo3g | Original post link

Solved: First, use the round function to round the field to 6 decimal places (or a longer number of decimal places that satisfies your calculation rounding), and then use the avg function. The result will retain many decimal places.

5
| username: Kongdom | Original post link

Understood. Please provide the Chinese text you would like translated to English.

6
| username: 像风一样的男子 | Original post link

Learned something new again.

7
| username: 哈喽沃德 | Original post link

Your cousin is impressive, must have a technical background.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.