-1
SELECT 
   SUM(id_fatura) as 'Val1',
   SUM(id_linha_fatura) as 'Val2',
   SUM(id_contrato) as 'Val3',
   SUM(id_servico) as 'Val4',
   SUM(valor) as 'Val5',
   (SUM(Val1) + SUM(Val2) + SUM(Val3) + SUM(val4) + SUM(val5)) as 'soma_faturas'
FROM DETALHE_FATURA;

Appear a error

SQL Error [923] [42000]: ORA-00923: FROM keyword not found where expected.

can anyone explain me why is appearing me this error

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
Frederico Azevedo
  • 1
  • 1
  • Are you sure you're using MySQL? (The error message looks more like Oracle.) – jarlh Jan 15 '20 at 11:45
  • no, i´m using oracle – Frederico Azevedo Jan 15 '20 at 11:54
  • Thank Ian, who has already replaced the tag. – jarlh Jan 15 '20 at 11:56
  • 1) Quoted aliases, remove qoutes 2) using aliases instead of fieldnames `SUM(Val1) + SUM(Val2)+...` instead of `SUM(id_fatura)+SUM(id_linha_fatura)+...`. – Akina Jan 15 '20 at 12:09
  • And again - the question closed with the reference to the answers which do NOT solve. – Akina Jan 15 '20 at 12:11
  • i tried a different way, SELECT ((id_fatura) + (id_linha_fatura) + (id_contrato) + (id_servico) + (valor)) AS total_fatura FROM DETALHE_FATURA GROUP BY id_fatura, ID_LINHA_FATURA, ID_CONTRATO, ID_SERVICO ,VALOR; and it worked – Frederico Azevedo Jan 15 '20 at 12:12
  • I do not recommend. In such case server cannot re-use already calculated separate sums and must additionally summarize separate fields of each record then sum them into one. Excess work without any profit except a lot of economized bytes. – Akina Jan 15 '20 at 12:13

1 Answers1

1

Only use single quotes for string and date constants. Try fixing that:

SELECT SUM(id_fatura) as Val1,
       SUM(id_linha_fatura) as Val2,
       SUM(id_contrato) as Val3,
       SUM(id_servico) as Val4,
       SUM(valor) as Val5,
       (SUM(id_fatura) + SUM(id_linha_fatura) + SUM(id_contrato) + SUM(id_servico) + SUM(valor)) as soma_faturas
FROM DETALHE_FATURA;

Although some databases do allow single quotes for column aliases, they are a bad idea, because they confuse the name of a column with the values in the columns. Identifiers and values are different things.

In addition, you need to repeat the expressions for the soma_faturas column. You cannot refer to the aliases (which is apparently what you want to do). You could use a CTE or subquery if the expressions are complicated.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786