SQL SUM problem

[chris-uk-lad]

Hi all!
Ive created this code to read in data from several tables but i have a problem with SUM. With this code im trying to add together all the null values that have the most recent date but all the SUM commandi have does is multiply the number by 3 :x

SELECT        A.REFNO, MAX(B.AMT), C.STATUS, C.ENDD, C.PAMT, SUM(C.PAMT)
FROM                BASIC A 
LEFT JOIN         TableB B ON B.REFNO = PD.REFNO 
AND                 A.BGROUP = B.BGROUP 
LEFT JOIN    TableC ON A.REFNO = C.REFNO
AND     A.BGROUP = PH1.BGROUP
LEFT JOIN    TableC C ON B.REFNO = PS1.REFNO
AND     A.BGROUP = C.BGROUP
WHERE A.REFNO = 'A170580' AND C.ENDD IS NULL
GROUP BY A.REFNO, C.STATUS, C.ENDD, C.PAMT

This is the output i get, instead of adding together 961.8 and 1274.52, it just multiplys them :(

NULL - 961.8 - 2885.4
NULL - 1274.52 - 3823.56

All help appreciated

[tesuji]

hi,

there are some discrepancies:

1. What are PD.REFNO, PH1.BGROUP, PS1.REFNO ?

2. Is there a self-join: LEFT JOIN TableC LEFT JOIN TableC C ???

3. TableC of first left join doesn't have alias.

4. Select has 6 columns but only 3 results are showing.

Btw, threefold results may likely occur by adding same values three times. Such stuff happens if joining conditions are wrongly chosen what then results in undesirable cross products (cartesian products).

-----
tesu

[chris-uk-lad]

My apologies, i changed some of the syntax to make it more readable, hence the discrepencies you outline, guess i fail lol. This is the unaltered code.

SELECT        B.REFNO, MAX(PH1.PH02D), PS1.STATUS, PS1.ENDD, SUM(PS1.PAMT)
FROM                BASIC B 
LEFT JOIN         Pre Date PD ON B.REFNO = PD.REFNO 
AND                 B.BGROUP = PD.BGROUP 
LEFT JOIN    Pre Holding PH1 ON B.REFNO = PH1.REFNO
AND     B.BGROUP = PH1.BGROUP
LEFT JOIN    Pre Sum PS1 ON B.REFNO = PS1.REFNO
AND     B.BGROUP = PS1.BGROUP
WHERE B.REFNO = 'A170580' AND PS1.ENDD IS NULL
GROUP BY B.REFNO, PS1.STATUS, PS1.ENDD, PS1.PAMT

I was only showing that output as a sample as only the final value is whats affected, so when End Date is Null in several rows, the PAMT values are added together

[tesuji]

And also that "unaltered" code went through some modification meanwhile, LOL