mysql sum 字符串_MySQL替换字符串SUM然后总计

总列在原始列值上进行求和，在选择中定义的别名不会在同一个选择中使用。

您可以在进行总列计算时重复替换语句

SUM( REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')

+ REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))

Total,

而不是

SUM(es_reserve+structure_reserve) as total

查询变为，按照评论中的要求排序。

SELECT insurance_carrier as ID, SUM(REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')) AS es_reserve, SUM(REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))AS structure_reserve, SUM( REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','') + REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-','')) as Total FROM job_tbl WHERE job_status NOT IN(2,4,6,7,9) AND insurance_carrier !=0 AND FROM_UNIXTIME(date_of_loss,'%m') = MONTH(NOW()) AND FROM_UNIXTIME(date_of_loss,'%Y') = YEAR(NOW()) GROUP BY insurance_carrier

order by SUM( REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','') + REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-','')) desc

OR

将它用作子查询

SELECT T.*, SUM(es_reserve+structure_reserve) AS total

FROM

(

SELECT

insurance_carrier as ID, SUM(REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')) AS es_reserve,

SUM(REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))AS structure_reserve

FROM job_tbl

WHERE

job_status NOT IN(2,4,6,7,9) AND

insurance_carrier !=0 AND

FROM_UNIXTIME(date_of_loss,'%m') = MONTH(NOW()) AND

FROM_UNIXTIME(date_of_loss,'%Y') = YEAR(NOW())

GROUP BY insurance_carrier

) T