mysql数据库聚合统计字段精度出错

有个大屏展示的项目，发现统计出来的数据跟对应业务那边的统计对不上，在小数位后面出现不一致问题，核对sql也没有发现明显的错误。

-- 原sql
SELECT
	sum( balance) AS sum ,type AS currency
FROM
	m_user u
	LEFT JOIN m_output_group_user ogu ON ogu.user_id = u.id AND ogu.deleted = 0
	LEFT JOIN m_output_group og ON og.id = ogu.group_id AND og.deleted = 0
	INNER JOIN m_user_wallet muw ON muw.user_id = u.id
WHERE
	u.deleted = 0
GROUP BY
	type;

拉着对应业务模块的开发一起排查问题，发现，他们在建表的时候‘balance’字段使用的是varchar类型。且最初查询出来的数据长度意料外的长。怀疑是sql聚合处理的时候隐式转换导致的精度问题。

解决方案就是在统计的时候对字段进行类型转换

SELECT
	sum( CONVERT(balance,DECIMAL(20,8) )) AS sum ,type AS currency
FROM
	m_user u
	LEFT JOIN m_output_group_user ogu ON ogu.user_id = u.id AND ogu.deleted = 0
	LEFT JOIN m_output_group og ON og.id = ogu.group_id AND og.deleted = 0
	INNER JOIN m_user_wallet muw ON muw.user_id = u.id
WHERE
	u.deleted = 0
GROUP BY
	type;

最终核对数据与业务统计一致。