Unanswered: Expert needed for this one( Select with sums on self joined table)
I have a services table that holds two types of records(new & old) and among the fields I have is Price. I need to add the total Price of all the new records and all total price of the old records. so I came up with this code.
SELECT Sum(tbl1.Price) AS OldCost, tbl1.ID, Sum(tbl2.Price) AS NewCost, tbl2.ID
FROM tblServices AS tbl1 INNER JOIN tblServices AS tbl2 ON tbl1.ClientID = tbl2.ClientID
WHERE ((tbl1.Type)="O") AND ((tbl2.Type)="N"))
GROUP BY tbl1.ClientID, tbl2.ClientID;
Now this almost works. except for the fact that it is doing some weird stuff by multiplying the totals times the number of records that it is pulling or something really close to that. I have never seen this before.
As a end Result I want three fields per record.
Total of New Services, Total of Old Services, and The client ID.
I have looked at it for 3 hrs and tryed everything I know to try. If I separate the query into two separate ones ( 1 for new and one for Old) it works perfectly but combinding them it just craps out.
Any Advice on what I am doing wrong.?
I really need some help....