Preventing the repetition of charges when adding to columns?

[jtok]

I'm working with MS SQL Server 2005. I am trying to add two columns together, and I have more or less succeeded. However, it obviously adds the entirety of each column, rather than calculating for each row.

I can't just include each column in the original SELECT statement, because it causes some of the charges to be duplicated.
(The reason for this is that if multiple services or parts are purchased, the results include every combination of parts and service. This, of course, results in a repetition of charges.)
I am open to completely changing the way this search is done, or even using a stored procedure to accomplish it. I merely would like it to work.

Here is basically what I a working with. I simplified it as much as I felt safe doing.

SELECT CUSTOMER.*, PROBLEM.ID, PROBLEM.STORE_ID,
 
((SELECT SUM(PROBLEM_SERVICE_CHARGE.ChargeAmount) 
	FROM PROBLEM_SERVICE_CHARGE 
	INNER JOIN PROBLEM ON PROBLEM_SERVICE_CHARGE.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0)

 + --Note the addition of these two SELECT statements.

(SELECT SUM(PART.PartCharge*1.0625) --The 1.0625 just takes care of the tax on the parts
	FROM PART
	INNER JOIN PROBLEM_PART ON PART.ID = PROBLEM_PART.PART_ID
	INNER JOIN PROBLEM ON PROBLEM_PART.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0)) 
AS Charge --The two added select statements become the Charge column

FROM [CUSTOMER] 
INNER JOIN CUSTOMER_SYSTEM ON CUSTOMER.ID = CUSTOMER_SYSTEM.CUSTOMER_ID 
INNER JOIN [SYSTEM] ON CUSTOMER_SYSTEM.SYSTEM_ID = SYSTEM.ID 
INNER JOIN PROBLEM ON SYSTEM.ID = PROBLEM.SYSTEM_ID 
INNER JOIN PROBLEM_SERVICE_CHARGE ON PROBLEM.ID = PROBLEM_SERVICE_CHARGE.PROBLEM_ID 
LEFT JOIN PROBLEM_PART ON PROBLEM.ID = PROBLEM_PART.PROBLEM_ID 
LEFT JOIN PART ON PROBLEM_PART.PART_ID = PART.ID 

WHERE PROBLEM.PickedUp = 0

ORDER BY [LastName], [FirstName]

[jtok]

I finally came up with a solution to this problem, and thought I'd share it.
I created a view for each of the subqueries like so:

CREATE VIEW Pickup_ServiceCharge
AS
SELECT DISTINCT CUSTOMER.ID, SUM(isnull(PROBLEM_SERVICE_CHARGE.ChargeAmount,0)) AS ServiceCharge
	FROM PROBLEM_SERVICE_CHARGE 
	INNER JOIN PROBLEM ON PROBLEM_SERVICE_CHARGE.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0
	GROUP BY CUSTOMER.ID;
GO

CREATE VIEW Pickup_PartCharge
AS
SELECT DISTINCT CUSTOMER.ID, SUM(isnull((PART.PartCharge*1.0625),0)) AS PartCharge
	FROM PART
	INNER JOIN PROBLEM_PART ON PART.ID = PROBLEM_PART.PART_ID
	INNER JOIN PROBLEM ON PROBLEM_PART.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0
	GROUP BY CUSTOMER.ID;
GO

Then I LEFT JOINed the views in the existing query ON the CUSTOMER.ID, making sure to use isnull() to give a default value of 0.

SELECT DISTINCT CUSTOMER.*, PROBLEM.ID, PROBLEM.STORE_ID, (isnull(Pickup_ServiceCharge.ServiceCharge,0) + isnull(Pickup_PartCharge.PartCharge,0)) AS Charge
FROM [CUSTOMER] 
INNER JOIN CUSTOMER_SYSTEM ON CUSTOMER.ID = CUSTOMER_SYSTEM.CUSTOMER_ID 
INNER JOIN [SYSTEM] ON CUSTOMER_SYSTEM.SYSTEM_ID = SYSTEM.ID 
INNER JOIN PROBLEM ON SYSTEM.ID = PROBLEM.SYSTEM_ID 
LEFT JOIN Pickup_ServiceCharge ON CUSTOMER.ID = Pickup_ServiceCharge.ID
LEFT JOIN Pickup_PartCharge ON CUSTOMER.ID = Pickup_PartCharge.ID
WHERE PROBLEM.PickedUp = 0
ORDER BY [LastName], [FirstName]

Viola! Problem solved. If there are any questions, just post them in this thread and I'll get them.