Help With Code Not Grouping By ID

[sk1hotpepr]

I wrote this code to sum four fields based on the ContractorID grouping (there's a left join to get Contractor info from a different table and list the contract price even if there are no change orders). When I run the procedure, I'm getting the overall sum for the project number, not grouped. Can anyone figure out what I'm coding wrong here? Any help is appreciated.
Thanks,
Sherry

ALTER PROCEDURE dbo.spBudget
(@pnum nvarchar(50))
AS SELECT     	PC.Firm, PC.ActBid, 
		(SUM(CASE when (CT.ApprovedCost IS NOT NULL) THEN CT.ApprovedCost ELSE 0 END)) AS AppCst, 
		(SUM(CASE when (CT.GACost IS NOT NULL) THEN CT.GACost ELSE 0 END)) AS GAC, 
		(SUM(CASE when (CT.SDCost IS NOT NULL) THEN CT.SDCost ELSE 0 END)) AS SDC,
                        (SUM(CASE when (CT.PropCost IS NOT NULL) THEN CT.PropCost ELSE 0 END)) AS Prop, 
		PI.ProjectName, IDC.ContractorIDDescription, PC.ProjectNumber, PC.CID
FROM dbo.ChangeOrderTable CT RIGHT OUTER JOIN dbo.ProjectContractor PC ON CT.ProjectNumber = PC.ProjectNumber INNER JOIN 
		dbo.ProjectInformation PI ON PC.ProjectNumber = PI.ProjectNumber INNER JOIN
		dbo.IDContractor IDC ON PC.CID = IDC.ContractorID
WHERE PC.ProjectNumber = @pnum 
GROUP BY PC.Firm, PC.CID, PC.ActBid, PC.ProjectNumber, PI.ProjectName, IDC.ContractorIDDescription

 

[rac2]

SELECT
         PC.Firm,
         PC.ActBid, 
        (SUM(CASE when (CT.ApprovedCost IS NOT NULL) THEN CT.ApprovedCost ELSE 0 END)) AS AppCst, 
        (SUM(CASE when (CT.GACost IS NOT NULL) THEN CT.GACost ELSE 0 END)) AS GAC, 
        (SUM(CASE when (CT.SDCost IS NOT NULL) THEN CT.SDCost ELSE 0 END)) AS SDC,
                        (SUM(CASE when (CT.PropCost IS NOT NULL) THEN CT.PropCost ELSE 0 END)) AS Prop, 
        PI.ProjectName,
        IDC.ContractorIDDescription,
        PC.ProjectNumber,
        PC.CID
FROM dbo.ChangeOrderTable CT

RIGHT OUTER JOIN dbo.ProjectContractor PC ON CT.ProjectNumber = PC.ProjectNumber
INNER JOIN 
        dbo.ProjectInformation PI ON PC.ProjectNumber = PI.ProjectNumber

INNER JOIN
        dbo.IDContractor IDC ON PC.CID = IDC.ContractorID

WHERE PC.ProjectNumber = @pnum 

GROUP BY PC.Firm,
         PC.CID,
         PC.ActBid,
         PC.ProjectNumber,
         PI.ProjectName,
         IDC.ContractorIDDescription

Why not start with the contractor table as the basis of the query, since you wish to have every contractor on a project listed whether they have a change order or not. Assuming all of the contractors are in the table, IDContractor -

FROM dbo.IDContractor IDC

But just the contractors on the specific project. So JOIN the ProjectContractor contractor table. This is like a filter on the list of contractors.

FROM dbo.IDContractor IDC
JOIN dbo.ProjectContractor PC ON PC.CID = IDC.CID
           AND PC.ProjectNumber = @pnum

Of course the JOIN uses the contractor ID instead of ProjectNumber. I dont see a column in your post giving the name of the contractor id column in the IDContractor table so I made it IDC.CID.

Then LEFT JOIN the possibly deficient table, ChangeOrderTable. This is probably where the interesting facts are.

FROM dbo.IDContractor IDC
JOIN dbo.ProjectContractor PC ON PC.CID = IDC.CID
           AND PC.ProjectNumber = @pnum
LEFT JOIN dbo.ChangeOrderTable CT ON CT.ProjectNumber = PC.ProjectNumber

Notice that the JOIN condition for ChangeOrderTable is also like a filter. The only ProjectNumber in the result is @pnum, just the change orders for that project will be included.

With the additional JOINs for descriptive columns, GROUP BY might yield results for each contractor on project @pnum whether or not there are change orders.

FROM dbo.IDContractor IDC
JOIN dbo.ProjectContractor PC ON PC.CID = IDC.CID
           AND PC.ProjectNumber = @pnum
LEFT JOIN dbo.ChangeOrderTable CT ON CT.ProjectNumber = PC.ProjectNumber
JOIN dbo.ProjectInformation PI ON PI.ProjectNumber = PC.ProjectNumber

All of the above is based on the assumption that ProjectContractor is an association table that simply tells which contractors work on which projects; and the IDContractor table has the descriptive information about the contractors.

Looking at the SELECT list I see PC.Firm, PC.CID, PC.ActBid,
PC.ProjectNumber which are descriptive items all from the ProjectContractor table and that contradicts my assumption.

So this is probably all wrong.