Averageing a the total of sums of multi columns!

[scottyjohn]

Hi all,
I have a query which runs against one table and the query I am attempting looks like this....

SELECT
SOFTPHONE.BRAND,
Count(SOFTPHONE.BRAND) AS CountOfBRAND,
ROUND(Avg([SOFTPHONE].[TT])) AS AVGTT,
ROUND(Avg([SOFTPHONE].[HT])) AS AHT,
ROUND(Avg([SOFTPHONE].[ACWT])) AS AVGACW,
Avg(Sum([TT]+[HT]+[ACWT])) AS AHT
FROM SOFTPHONE
GROUP BY SOFTPHONE.BRAND, Avg(Sum([TT]+[HT]+[ACWT]));

After adding the calculated column AHT, I get the error back "cannot aggregate function in expression" Im assuming that it doesnt like me trying to average a sum?
Also when I try to just get the sum of the three columns TT HT and ACWT, using [SOFTPHONE]![TT]+[SOFTPHONE]![HT]+[SOFTPHONE]![ACWT], I get a cocatenation of those fields rather than an added total. I dont understand what is going on. can anyone help?

John

 

[Golom]

I suspect that what's going on internally is
[tt]
Brand TT HT ACWT
A 1 2 3
A 4 5 6

and then

SUM(TT+HT+ACWT) ---> 21

AVG(21) ---> 21 / [red]??[/red]
[/tt]
AVG needs some "number of occurrences" to use when computing the average but, since SUM is itself an aggregate function, it's not clear what the "number of occurrences" would be. Do you mean just AVG(TT+HT+ACWT) since it computes a SUM and then divides it by the number of occurrences?

Can you describe in words, what the meaning of "Avg(Sum([TT]+[HT]+[ACWT]))" is?

Getting a concatenation of fields rather than a sum implies that the data types of the fields are text rather than numbers.

 

[scottyjohn]

Hi Golom,
A sample of the data is below (each record is a call into a contact centre, TT is talktime, HT is hold time, and ACWT is after call work time)


BRAND TT HT ACWT
Brand1 100 50 50
Brand2 100 100 100
Brand1 50 50 100
Brand2 200 0 10

I then group the data to make

BRAND CALLS TT HT ACWT
Brand1 2 150 100 150
Brand2 2 300 100 110

What I want "Avg(Sum([TT]+[HT]+[ACWT]))" to do is to give me the Average Handle time which is Total TT + Total HT + Total ACWT / Calls, per brand. Hope this makes more sense?


John

 

[PHV]

SELECT BRAND, Count(*) AS CALLS,
ROUND(Avg([TT])) AS AVGTT,
ROUND(Avg([HT])) AS AVGHT,
ROUND(Avg([ACWT])) AS AVGACWT,
ROUND(Avg([TT]+[HT]+[ACWT])) AS AHT
FROM SOFTPHONE
GROUP BY BRAND

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

OK. I think that you just want [blue]AVG([TT]+[HT]+[ACWT])[/blue]

The AVG function computes a SUM and divides by the number of occurrences. You will also just

Group By Brand

You want the average computed for each brand but the average doesn't define the group ... it is a result of the group.

 

[MichaelRed]

but ther average of the average is not what is typically wanted. this is called a simple average and ignores the issue that one of your averages may encompass a L O T more values than another. the more typical resquest means (implies) a weighted average which includes the number of units in the calculation.


a BRIEF example:

100 widgets at a average price of $1.53

[tab][tab][tab][tab] AND

2 super widgets at an averge price or $2.12

do you want to say 102 widgets at an average price of $ 1.81 (simple average)

[tab][tab][tab][tab] OR

102 widgets at an average price of $1.54

??????????????????







MichaelRed

 

[scottyjohn]

Hi all,
I remembered a thread a while back relating to a data type mismatch and noted from this that when adding two fields they were being cocatenated instead of added.
I applied the solution for that problem to this and now appear to have a resolution!

ROUND(Avg(Val([TT])+Val([HT])+Val([ACWT])))AS AHT

This appears to give me what I need, many thanks for all the help

John