Subject | problem of using SUM function with the the HAVING clause? |
---|---|

Author | innoy1k |

Post date | 2004-08-02T00:26:08Z |

I have the following queries and results:

1) select filenumber, sum(DB_Amount), sum(CR_Amount)

from ledger where VORP > "" group by filenumber, VORP

having sum(DB_AMOUNT) - sum(CR_AMOUNT) = 0

sample results:

FILENUMBER SUM SUM1

=========== ======== ========

8552 15 15

29450 3756.94 3756.94

29657 920.19 920.19

29863 100 100

29956 19000 19000

29958 1518 1518

29970 100 100

30532 3000 3000

2) select filenumber, sum(DB_Amount), sum(CR_Amount)

from ledger where VORP > "" group by filenumber, VORP

having sum(DB_AMOUNT) - sum(CR_AMOUNT) <> 0

sample results:

FILENUMBER SUM SUM1

=========== ======== ========

26062 2926 4646

29933 7717.48 7717.48

30190 14476.03 14476.03

30473 533.48 533.48

30497 3323.7 3323.7

30525 1184.41 1184.41

30529 1262.88 1262.88

Both results were not meeting the expectation. The selection from

result 1) does not return filenumber 29933, but the selection from

result 2) includes those = 0.

Is there a problem in my SQL using the SUM function?

Please help.

Thanks

Duncan

1) select filenumber, sum(DB_Amount), sum(CR_Amount)

from ledger where VORP > "" group by filenumber, VORP

having sum(DB_AMOUNT) - sum(CR_AMOUNT) = 0

sample results:

FILENUMBER SUM SUM1

=========== ======== ========

8552 15 15

29450 3756.94 3756.94

29657 920.19 920.19

29863 100 100

29956 19000 19000

29958 1518 1518

29970 100 100

30532 3000 3000

2) select filenumber, sum(DB_Amount), sum(CR_Amount)

from ledger where VORP > "" group by filenumber, VORP

having sum(DB_AMOUNT) - sum(CR_AMOUNT) <> 0

sample results:

FILENUMBER SUM SUM1

=========== ======== ========

26062 2926 4646

29933 7717.48 7717.48

30190 14476.03 14476.03

30473 533.48 533.48

30497 3323.7 3323.7

30525 1184.41 1184.41

30529 1262.88 1262.88

Both results were not meeting the expectation. The selection from

result 1) does not return filenumber 29933, but the selection from

result 2) includes those = 0.

Is there a problem in my SQL using the SUM function?

Please help.

Thanks

Duncan