Синтаксическая ошибка sum

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

1

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 sl.quantity_loss * p.marginal_value END) AS value_loss,
        SUM(sl.duration) AS duration,
        SUM(sl.quantity_loss) AS quantity_loss,
        COUNT(*) AS count
FROM
    sub_losses AS sl

1

Answers

• What version of SQL Server are you using?  The COMPUTE keyword was recently deprecated in SQL Server 2012.  If you are trying to just get the sum, you could return it in your select statement:

  select sum(Amount)
  from [grant]
  

  Thanks,
  Sam Lester (MSFT)

  ---

  This posting is provided «AS IS» with no warranties, and confers no rights. Please remember to click «Mark as Answer» and «Vote as Helpful» on posts that help you. This can be beneficial to other community members reading
  the thread.

  • Proposed as answer by
    R. Maier
    Wednesday, July 11, 2012 7:39 AM
  • Marked as answer by
    Naomi N
    Monday, November 5, 2012 3:01 AM

• In this case this is the answer — COMPUTE command is deprecated in SQL 2012, so you can not use it. You need to use

  More to the point, COMPUTE has been deprecated since God knows when. In SQL 2012 it was removed.

  A deprecated feature is a feature that still exists, but which is slated for removal at some point in the future and therefore should be avoided.

  I will have to admit having use COMPUTE for ad hoc queries when I have been lazy, but it was a completely crazy feature, so there no reason to deplore its disappearance.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Proposed as answer by
    Satheesh Variath
    Wednesday, July 4, 2012 6:31 AM
  • Marked as answer by
    amber zhang
    Wednesday, July 11, 2012 7:15 AM

• In this case this is the answer — COMPUTE command is deprecated in SQL 2012, so you can not use it. You need to use

  select sum(Amount) as TotalAmount from [Grant]

  ---

  For every expert, there is an equal and opposite expert. — Becker’s Law

  My blog

  • Marked as answer by
    Kalman Toth
    Monday, November 5, 2012 2:25 PM

• • Proposed as answer by
    Naomi N
    Tuesday, July 3, 2012 7:24 PM
  • Marked as answer by
    Kalman Toth
    Monday, November 5, 2012 2:24 PM

• The COMPUTE clause was an old 1970’s Sybase  feature that was removed in 2012. However, look at the ROLLUP and CUBE we today. They are ANSI Srandard. 

  ---

  —CELKO— Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking
  in Sets / Trees and Hierarchies in SQL

  • Proposed as answer by
    Naomi N
    Tuesday, July 3, 2012 8:54 PM
  • Marked as answer by
    amber zhang
    Wednesday, July 11, 2012 7:15 AM

Я пытаюсь запустить следующий запрос, и я получаю следующую ошибку:

У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с sum (sl.duration) в качестве продолжительности, sum (sl.quantity_loss) как 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

Любая идея, почему возникает синтаксическая ошибка?

04 дек. 2017, в 11:45

Поделиться

Источник

2 ответа

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 sl.quantity_loss * p.marginal_value END) AS value_loss,
        SUM(sl.duration) AS duration,
        SUM(sl.quantity_loss) AS quantity_loss,
        COUNT(*) AS count
FROM
    sub_losses AS sl

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