complex aggregate function

[jhardman]

I have a table that looks like this:

location	group	agentsReady	contractsCompleted
location1	group1	12	14
location1	group2	12	6
location2	group1	25	12
location2	group2	25	20
location2	group3	25	2

The agentsReady field is duplicating in all of the groups for the same location (in other words the 12 agentsReady in group1 are the same 12 agentsReady in group2) Grouping by location would be really easy, but what I really need is a grand total, which in the case above contractsComple ted would be 54 (very easy sum() function) but the agentsready should be 37, not 99. Is there any obvious way to group or aggregate these numbers so that I get the expected number? like maybe group everything by location and take the sum of contracts and max of agents, then take the total of everything?

I have a workaround in place, but it is pretty cumbersome, manipulating the data programatically . I would really like to get the total from the db.

Jared

[ck9663]

How about those 25s? Are they all the same?

-- CK

[jhardman]

How about those 25s? Are they all the same?

-- CK

yes, the agents are assigned per location, but the contracts are assigned per group. Does this make sense?

Jared

[Jerry Winston]

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 [INDENT]Avg(AgentsReady) [AgentsReady],[/INDENT][INDENT]Sum(ContractsCompleted) [ContractsCompleted][/INDENT]from
([INDENT]
select [INDENT][Group],
Sum(AgentsReady) [AgentsReady],
Sum(ContractsCompleted) [ContractsCompleted] [/INDENT]
from [INDENT]dbo.tbl_Agent[/INDENT]
group by [Group] 
[/INDENT]
) T

AgentsReady ContractsCompleted
37             	52

I first grouped by [Group] to get the correct number of [Agents] with a sum, I also summed the ContractsComple ted column. I grouped by [group] because the [AgentsReady] column could not be expressed as a straight sum like the contractsComple ted column.

I wrapped the whole thing in another select statement that took the average of the [AgentsReady] column and finished summing the [ContractsComple ted] 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.

[jhardman]

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.

Thanks for your solution, that is the type of answer I was looking for.

How perceptive! Yes, this is the result of a complex join, the data is much more segmented than this - in 4 or 5 tables, I was trying to simplify the question. I will take your comments to heart about whether I'm already trying to do too much with this one view, but I do need the end result to be one row of rolled up data, and this was the best solution I could come up with.

Jared