odd query results

[aebstract]

I've got this:

 

    SUM(postransaction.amount) AS PAYMENTSAMOUNT

 

in this query:

 

SELECT SO.num AS "SO", SO.dateCompleted AS "DateCompleted", SO.dateIssued as "DateIssued",
    COALESCE(SO.totalTax, 0) as "Tax", COALESCE(POSTransaction.Amount, 0) AS "POSAmount",
    POSTransaction.dateTime as "POSDate", PaymentMethod.name AS "PaymentName", Customer.name as "Customer", so.customerpo as "customerpo",
    COALESCE(SUM(SOItem.totalPrice), 0) AS totalPrice, MAX(SOItem.dateScheduledFulfillment) AS DateScheduledFulfillment,
    PaymentTerms.netDays as "NetDays", PaymentTerms.nextMonth as "NextMonth",
    PaymentMethod.Name as "PaymentType", company.name AS company, COALESCE(postcatch.totalpaid, 0) AS totalpaid,
    COALESCE(postcatchcount.countpaid,1) as catchcount, 
    SUM(postransaction.amount) AS PAYMENTSAMOUNT

FROM Customer, SOItem, PaymentTerms, SO
    LEFT JOIN POSTransaction ON POSTransaction.soID = SO.ID
    LEFT JOIN PaymentMethod ON PaymentMethod.ID = POSTransaction.paymentMethodID
    JOIN company ON company.id = 1
    LEFT JOIN (SELECT SUM(amount) AS totalpaid, soid
                 FROM postransaction
                GROUP BY soid) postcatch ON postcatch.soid = so.id
    LEFT JOIN (SELECT Count(amount) AS countpaid, soid
                 FROM postransaction
                GROUP BY soid) postcatchCount ON postcatchCount.soid = so.id



WHERE Customer.ID = SO.customerID
AND SOItem.SOID = SO.ID
AND PaymentTerms.ID = SO.paymentTermsID
AND SOItem.typeID <> 40
AND Customer.ID LIKE $P{customerID}
AND SOItem.statusID <> 70
AND SO.statusID IN($P{ckIssued},$P{ckInProgress},$P{ckFulfilled},$P{ckClosedShort})



GROUP BY SO.num, SO.dateCompleted, SO.dateIssued, SO.statusID, SO.totalTax,
    POSTransaction.amount, POSTransaction.dateTime, PaymentMethod.name, Customer.name,
    PaymentTerms.netDays, PaymentTerms.typeID, PaymentTerms.nextMonth, company.name,
    postcatch.totalpaid, postcatchcount.countpaid, so.customerpo



ORDER BY Customer.name, SO.num, POSTransaction.dateTime

 

I'm attempting to take the payments and add them together to see how much has been payed towards the order. I've made a variable out of the PAYMENTAMOUNTS results. It is giving me odd results though, for example on a specific order that I am looking at. The total is 1809.69. The customer has made a payment of 465.69 which should leave us at 1344. Right now I am just trying to display the total payments made, which is 465.69. So when I call that variable, I am getting 3259.83. I am not sure what is causing this to happen. Does anyone have an idea of what might cause this?

[fenway]

Solved how? You've started a lot of threads on what seem to be related queries.