Nested SELECT Statement

Question

SQL is not my forte, but I'm working on it - thank you for the replies.

I am working on a report that will return the completion percent of services for indiviudals in our contracts. There is a master table "Contracts," each individual Contract can have multiple services from the "services" table, each service has multiple standards for the "standards" table which records the percent complete for each standard.

I've gotten as far as calculating the total percent complete for each individual service for a specific Contract_ServiceID, but how do I return all the services percentages for all the contracts? Something like this:

Contract           Service           Percent complete

---

abc Company   service 1        98%
abc Company   service 2      100%
xyz Company   service 1        50%

Here's what I have so far:

SELECT  
    Contract_ServiceId, 
    (SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100 as "Percent Complete"     
FROM    dbo.Standard sta WITH (NOLOCK) 
        INNER JOIN dbo.Contract_Service conSer ON sta.ServiceId = conSer.ServiceId
        LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
            AND conSer.StandardReportId = standResp.StandardReportId

WHERE Contract_ServiceId = '[an id]'
GROUP BY Contract_ServiceID

This gets me too:

Contract_serviceid      Percent Complete

---

[an id]                        100%

EDIT: Tables didn't show up in post.

Answer 1

I'm not sure if I understand the problem, if the result is ok for a service_contract you canContract Service

SELECT con.ContractId, 
       con.Contract,
       conSer.Contract_ServiceID,
       conSer.Service, 
       (SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100 as "Percent Complete"         
FROM    dbo.Standard sta WITH (NOLOCK) 
        INNER JOIN dbo.Contract_Service conSer ON sta.ServiceId = conSer.ServiceId
        INNER JOIN dbo.Contract con ON con.ContractId = conSer.ContractId
        LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
                AND conSer.StandardReportId = standResp.StandardReportId
GROUP BY con.ContractId, con.Contract, conSer.Contract_ServiceID, conSer.Service

make sure you have all the columns you select from the Contract table in the group by clause