Subject | Re: How write a query with a progressive sum field |
---|---|

Author | Aage Johansen |

Post date | 2016-03-08T10:24:41Z |

Luigi Siciliano wrote:

<<

select

DT.DATA,

DT.DOCUMENTO_ID,

DT.NUMERO,

DT.SERIE,

DC.CARICO,

DC.SCARICO,

SUM(dc2.carico - DC2.SCARICO) AS saldo

from

...

but I have an issue:

if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or

subtracted by the number present in last row with

CARICO > 0 or SCARICO > 0

if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain

almost correct result but the rows that contain "CARICO = 0 and SCARICO

= 0" now contain SALDO = NULL, the others contains correct progressive sum.

What I'm wrong?

coalesce(sum(DC2.CARICO-DC2.SCARICO),0) as SALDO

--

Aage J.

<<

select

DT.DATA,

DT.DOCUMENTO_ID,

DT.NUMERO,

DT.SERIE,

DC.CARICO,

DC.SCARICO,

SUM(dc2.carico - DC2.SCARICO) AS saldo

from

...

but I have an issue:

if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or

subtracted by the number present in last row with

CARICO > 0 or SCARICO > 0

if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain

almost correct result but the rows that contain "CARICO = 0 and SCARICO

= 0" now contain SALDO = NULL, the others contains correct progressive sum.

What I'm wrong?

>>You could try

coalesce(sum(DC2.CARICO-DC2.SCARICO),0) as SALDO

--

Aage J.