'Total In' 是字符串。如果在数值表达式中使用它，那么mysql会将其转换为数字。这个数字是多少 0 . 绳子 'Total In' 与别名为的列没有任何关系 Total In .
在mysql中，在表达式中使用表别名基本上有两种选择：（1）重复表达式(2） 使用cte。

SELECT f.id_festival AS ID,
       CONCAT('£ ', FORMAT(f.festival_budget, 2)) AS Budget,
       CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL IN`,
       CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL OUT`,
       CONCAT('£ ',
              ROUND(f.festival_budget +
                    SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN p.pmt_amount
                             WHEN p.pmt_type = 'Payment OUT' THEN - p.pmt_amount
                             ELSE 0
                        END),
                    2)
             ) AS PROFIT
FROM festival f INNER JOIN
     payment p
     ON p.id_festival = f.id_festival
WHERE f.id_festival = 1
GROUP BY f.id_festival;

确实，在某些情况下，可以在子查询中使用别名来获得相同的效果。但我不建议这样做，因为我还没有找到能够保证这一点的文档。

请尝试：

SELECT ID, Budget, `TOTAL IN`, `TOTAL OUT`, CONCAT('£ ', ROUND(SUM((Budget   + `TOTAL IN`) - `TOTAL OUT` ))) AS PROFIT FROM 
 (SELECT f.id_festival AS ID, 
 CONCAT('£ ', FORMAT(f.festival_budget, 2)) AS Budget,
 CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN   p.pmt_amount ELSE 0 END), 2)) AS `TOTAL IN`,
 CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN   p.pmt_amount ELSE 0 END), 2)) AS `TOTAL OUT`,
 FROM festival f
 INNER JOIN payment p
 ON p.id_festival = f.id_festival
 WHERE f.id_festival = 1
 GROUP BY f.id_festival) a;

试试这个code:-

SELECT
  f.id_festival AS ID,
  CONCAT('£ ', FORMAT(f.festival_budget, 2)) AS Budget,
  CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL_IN`,
  CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL_OUT`,
  (SELECT
      CONCAT('£ ', ROUND(SUM(f.festival_budget + 'TOTAL_IN') - 'TOTAL_OUT', 2))  AS PROFIT
FROM festival f
  INNER JOIN payment p
    ON p.id_festival = f.id_festival
WHERE f.id_festival = 1
GROUP BY f.id_festival

无需再选择：

SELECT
  f.id_festival AS ID,
  CONCAT('£ ', FORMAT(f.festival_budget, 2)) AS Budget,
  CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL IN`,
  CONCAT('£ ', ROUND(SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN p.pmt_amount ELSE 0 END), 2)) AS `TOTAL OUT`,
  CONCAT('£ ', 
    ROUND(
        f.festival_budget 
        +ROUND(SUM(CASE WHEN p.pmt_type = 'Payment IN' THEN p.pmt_amount ELSE 0 END), 2)
        -ROUND(SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN p.pmt_amount ELSE 0 END), 2)
        , 2)
    ) AS `PROFIT`
FROM festival f
  INNER JOIN payment p
    ON p.id_festival = f.id_festival
WHERE f.id_festival = 1
GROUP BY f.id_festival