Subject | SUM(DC.I) / SUM(DC.Q) vs SUM(DC.I / DC.Q) why result is different? |
---|---|

Author | Luigi Siciliano |

Post date | 2016-02-29T14:19:14Z |

Hallo,

I have, in DC table, two rows with same data:

A_ID I Q

1 370 1

2 370 1

with this query:

SELECT FIRST 1

DT.D as DCU,

SUM(DC.I) / SUM(DC.Q) AS CU,

(SELECT

SUM(DC.I) / SUM(Q)

FROM

DOC_C DC

WHERE

DC.C > 0.00

AND DC.A_ID = :ID

) AS CM

FROM

DOC_C DC, DOC_T DT

WHERE

DC.DOC_T_ID = DT.ID

AND DC.C > 0.00

AND DC.A_ID = :ID

GROUP BY

DT.D

ORDER BY

DT.D

I obtain correct result:

CU = 370

CM = 370

but with this query:

SELECT FIRST 1

DT.D as DCU,

SUM(DC.I / DC.Q) AS CU,

(SELECT

SUM(DC.I) / SUM(Q)

FROM

DOC_C DC

WHERE

DC.C > 0.00

AND DC.A_ID = :ID

) AS CM

FROM

DOC_C DC, DOC_T DT

WHERE

DC.DOC_T_ID = DT.ID

AND DC.C > 0.00

AND DC.A_ID = :ID

GROUP BY

DT.D

ORDER BY

DT.D

I obtain wrong result:

CU = 740

CM = 370

Why?

What is the difference from "SUM(DC.I) / SUM(DC.Q)"

and "SUM(DC.I / DC.Q)"

Thanks

--

Luigi Siciliano

--------------------------

I have, in DC table, two rows with same data:

A_ID I Q

1 370 1

2 370 1

with this query:

SELECT FIRST 1

DT.D as DCU,

SUM(DC.I) / SUM(DC.Q) AS CU,

(SELECT

SUM(DC.I) / SUM(Q)

FROM

DOC_C DC

WHERE

DC.C > 0.00

AND DC.A_ID = :ID

) AS CM

FROM

DOC_C DC, DOC_T DT

WHERE

DC.DOC_T_ID = DT.ID

AND DC.C > 0.00

AND DC.A_ID = :ID

GROUP BY

DT.D

ORDER BY

DT.D

I obtain correct result:

CU = 370

CM = 370

but with this query:

SELECT FIRST 1

DT.D as DCU,

SUM(DC.I / DC.Q) AS CU,

(SELECT

SUM(DC.I) / SUM(Q)

FROM

DOC_C DC

WHERE

DC.C > 0.00

AND DC.A_ID = :ID

) AS CM

FROM

DOC_C DC, DOC_T DT

WHERE

DC.DOC_T_ID = DT.ID

AND DC.C > 0.00

AND DC.A_ID = :ID

GROUP BY

DT.D

ORDER BY

DT.D

I obtain wrong result:

CU = 740

CM = 370

Why?

What is the difference from "SUM(DC.I) / SUM(DC.Q)"

and "SUM(DC.I / DC.Q)"

Thanks

--

Luigi Siciliano

--------------------------