总列在原始列值上进行求和，在选择中定义的别名不会在同一个选择中使用。
您可以在进行总列计算时重复替换语句
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