[thelist] Query Quandry

[Jay Blanchard]

I am performing the following query;

SELECT  sot.BillingNumber,
        sot.BillDate,
        sot.SOCompletionDate,
        sot.SONumber,
        sot.PONumber,
        SUM(son.USONetChange) AS NetChange,
        SUM(son.USOFractionalAmount) AS FractionalAmount,
        SUM(son.USOOneTimeAmount) AS OneTimeAmount,
        SUM(son.USOBilledAmount) AS BilledAmount
FROM    tblOCCSvcOrdTransID00 sot,
        tblOCCSvcOrdTransNetEffect son
WHERE   son.BillDate = '2002-03-05'
AND     son.BillingNumber = '7105575015'
AND     sot.BillDate = son.Billdate
AND     sot.BillingNumber = son.BillingNumber
GROUP BY son.SONumber;

This returns BillingNumber, BillDate, SOCompletionDate, SONumber, and
PONumber as expected, but in each row NetChange, FractionalAmount,
OnTimeAmount, and BilledAmount come back as the same numbers. If I change
the GROUP BY to a unique value in tblOCCSvcOrdTransNetEffect the summed
numbers return correctly, but the 'sot' values are all the same. I don't
have the option of using UNION because I am using MySQL 3.2x. If I GROUP BY
sot.SONumber, son.RecordKey I get tblFOO x tblBAR the results (too many
squared).

I think now I am looking too closely at the problem since I have tried many
configurations. For each record returned here the BillingNumber and BillDate
will be the same. The SOCompletionDate, SONumber, PoNumber, and summed
amounts will all be different for returned record. To quote Streisand, "Rudy
can you hear me?"

Signed,

Going Grey In Texas

TIA!!

Jay