mysql - SQL 查询语法错误 : unexpected 'SUM' (sum)

Question

我正在尝试运行以下查询，但出现以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sum(sl.duration) as duration, sum(sl.quantity_loss) as quantity_loss'

SELECT 
   lr.uid AS loss_reason_uid,
   gl.uid AS gl_uid,
   lt.uid AS lt_uid,
   SUM(sl.duration * b.marginal_value_downtime) AS value_loss where en.state = 'downtime',
   SUM(sl.quantity_loss * p.marginal_value) as value_loss where en.state != 'downtime',
   SUM(sl.duration) AS duration,
   SUM(sl.quantity_loss) AS quantity_loss,
   COUNT(*) AS count
FROM
    sub_losses AS sl

知道为什么会出现此语法错误吗？

Answer 1

试试这个:除了上面的注释，不允许在单个查询 block 中多次使用相同的ALIAS 或COLUMN NAME所以我给 value_loss1 使它是唯一的。

SELECT 
   lr.uid AS loss_reason_uid,
   gl.uid AS gl_uid,
   lt.uid AS lt_uid,
   SUM(CASE WHEN en.state = 'downtime' THEN (sl.duration * b.marginal_value_downtime) ELSE 0 END) AS value_loss,
   SUM(CASE WHEN en.state != 'downtime' THEN (sl.quantity_loss * p.marginal_value) ELSE 0 END) AS value_loss1,
   SUM(sl.duration) AS duration,
   SUM(sl.quantity_loss) AS quantity_loss,
   COUNT(*) AS count
FROM sub_losses AS sl
GROUP BY lr.uid,
         gl.uid,
         lt.uid