By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

complex aggregate function

jhardman
Expert 2.5K+
P: 3,405
I have a table that looks like this:
Expand|Select|Wrap|Line Numbers
  1. location    group    agentsReady    contractsCompleted
  2. location1    group1    12    14
  3. location1    group2    12    6
  4. location2    group1    25    12
  5. location2    group2    25    20
  6. 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 contractsCompleted 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
Oct 16 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
How about those 25s? Are they all the same?

-- CK
Oct 17 '08 #2

jhardman
Expert 2.5K+
P: 3,405
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
Oct 17 '08 #3

Expert 100+
P: 145
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.
Expand|Select|Wrap|Line Numbers
  1. select 
    Avg(AgentsReady) [AgentsReady],
    Sum(ContractsCompleted) [ContractsCompleted]
    from
  2. (
  3. select 
    [Group],
  4. Sum(AgentsReady) [AgentsReady],
  5. Sum(ContractsCompleted) [ContractsCompleted] from 
    dbo.tbl_Agent
    group by [Group] 
  6. ) T
Expand|Select|Wrap|Line Numbers
  1. AgentsReady ContractsCompleted
  2. 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.
Oct 17 '08 #4

jhardman
Expert 2.5K+
P: 3,405
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
Oct 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.