If i understand you correctly all you want is a sum of agentsReady and a sum of Contracts completed. You're not particularly interested in what happend at what location or by which group. If that's the case this should do the trick.
-
select
Avg(AgentsReady) [AgentsReady],
Sum(ContractsCompleted) [ContractsCompleted]
from -
(
-
select
[Group],
-
Sum(AgentsReady) [AgentsReady],
-
Sum(ContractsCompleted) [ContractsCompleted] from
dbo.tbl_Agent
group by [Group] - ) T
-
AgentsReady ContractsCompleted
-
37 52
I first grouped by [Group] to get the correct number of [Agents] with a sum, I also summed the ContractsCompleted column. I grouped by [group] because the [AgentsReady] column could not be expressed as a straight sum like the contractsCompleted column.
I wrapped the whole thing in another select statement that took the average of the [AgentsReady] column and finished summing the [ContractsCompleted] column. At the second select statement, I did not have to use avg on the [AgentsReady] column, i could have used any aggregate function that would not affect the [AgentsReady] value like max or min.
Just my thoughts, but I'm not wholly convinced this table is doing the job it was intended to do. We sometimes find design issues when it comes time to query the data. It appears to me this table has the dual responsibility of tracking AgentsReady and Contracts Completed. The duplicate Location and Group Column values make the table look like the query results of a JOIN operation. Needless data duplication is a good indicator of whether or not your table is doing too much. I don't know how you use this table but I believe you should create an Agents table and a Contracts table.
