Luigi Siciliano luigisic@... [firebird-support] wrote:

I get exception: "invalid expression in the select list (not contained in either an aggregate function or the group by clause)".
I don't understand the GROUP BY clause who I need to write in sub-select because if I put the same as in select: "GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE" I get the exception: "cannot use an aggregate function in a GROUP BY clause."
I'm confused :(

Yeah, I wonder why that worked in the first place in your original select. You didn't group by DC.CARICO and DC.SCARICO there too.

But with a (SELECT)-field you don't need to group anymore.

Try this:

select
  DT.DATA,
  DT.DOCUMENTO_ID,
  DT.NUMERO,
  DT.SERIE,
  DC.CARICO,
  DC.SCARICO,
  (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2
   WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID)
   AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID)
  ) AS SALDO
from DOC_TESTA DT
JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID
WHERE DC.ARTICOLO_ID = :ID
ORDER BY DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE