SUM divided by COUNT Issue

Question

Post reply

SUM divided by COUNT Issue

ryan.quigley

SSC-Addicted

Points: 442
More actions
February 28, 2008 at 6:41 pm

#69975

Please see the SQL code below. In the 6, 7, 8, and 9 lines of the code, I'm dividing a SUM by a COUNT. However, it seems to be completely disregarding the COUNT portion and just listing the SUM result in my query. Why is this?
SELECT
AG.AGENT_GROUP_NAME + ' - ' + CONVERT(VARCHAR(12), AG.AGENT_GROUP_NUMBER) AS "Agent Group (Name & #)",
AP.APPLICATION_NAME + ' - ' + CONVERT(VARCHAR(12), AP.APPLICATION_NUMBER) AS "Application (Name & #)",
AG.ADVOCATE + ' - ' + CONVERT(VARCHAR(12), AG.EXTENSION_NUMBER) AS "Agent (Name & #)",
COUNT(CD.CALL_DETAIL_KEY) AS "Total Calls",
SUM(CD.TALKING_TIME) / COUNT(CD.CALL_DETAIL_KEY) AS "Average Talk",
SUM(CD.HOLD_TIME) / COUNT(CD.CALL_DETAIL_KEY) AS "Average Hold",
SUM(CD.WRAPUP_TIME) / COUNT(CD.CALL_DETAIL_KEY) AS "Average Wrap",
SUM(CD.TALKING_TIME + CD.HOLD_TIME + CD.WRAPUP_TIME) / COUNT(CD.CALL_DETAIL_KEY) AS "Average AHT"
FROM
F_CALL_DETAIL CD
INNER JOIN D_APPLICATION AP ON AP.APPLICATION_KEY = CD.APPLICATION_KEY AND AP.STATE = 1
INNER JOIN D_AGENT_PROXY AG ON AG.AGENT_PROXY_KEY = CD.AGENT_PROXY_KEY AND AG.STATE = 1
INNER JOIN D_DATE DT ON DT.DATE_KEY = CD.CALL_TERMINATION_DATE_KEY
WHERE
DT.CALENDAR_DATE_NAME = '02/21/2008'
AND
CD.CALL_TYPE IN (2, 15)
AND
CD.DISPOSITION IN (4, 5)
GROUP BY
AG.AGENT_GROUP_NAME + ' - ' + CONVERT(VARCHAR(12), AG.AGENT_GROUP_NUMBER),
AP.APPLICATION_NAME + ' - ' + CONVERT(VARCHAR(12), AP.APPLICATION_NUMBER),
AG.ADVOCATE + ' - ' + CONVERT(VARCHAR(12), AG.EXTENSION_NUMBER)
ORDER BY
"Agent Group (Name & #)",
"Application (Name & #)",
"Agent (Name & #)"

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this, Ryan:

SELECT "Agent Group (Name & #)",

"Application (Name & #)",

"Agent (Name & #)",

"Total Calls",

"Total Talk" / "Total Calls" AS "Average Talk",

"Total Hold" / "Total Calls" AS "Average Hold",

"Total Wrap" / "Total Calls" AS "Average Wrap",

("Total Talk" + "Total Hold" + "Total Wrap") / "Total Calls" AS "Average AHT"

FROM (

SELECT

AG.AGENT_GROUP_NAME + ' - ' + CONVERT(VARCHAR(12), AG.AGENT_GROUP_NUMBER) AS "Agent Group (Name & #)",

AP.APPLICATION_NAME + ' - ' + CONVERT(VARCHAR(12), AP.APPLICATION_NUMBER) AS "Application (Name & #)",

AG.ADVOCATE + ' - ' + CONVERT(VARCHAR(12), AG.EXTENSION_NUMBER) AS "Agent (Name & #)",

COUNT(CD.CALL_DETAIL_KEY) AS "Total Calls",

SUM(CD.TALKING_TIME) AS "Total Talk",

SUM(CD.HOLD_TIME) AS "Total Hold",

SUM(CD.WRAPUP_TIME) AS "Total Wrap"

FROM F_CALL_DETAIL CD

INNER JOIN D_APPLICATION AP

ON AP.APPLICATION_KEY = CD.APPLICATION_KEY AND AP.STATE = 1

INNER JOIN D_AGENT_PROXY AG

ON AG.AGENT_PROXY_KEY = CD.AGENT_PROXY_KEY AND AG.STATE = 1

INNER JOIN D_DATE DT

ON DT.DATE_KEY = CD.CALL_TERMINATION_DATE_KEY

WHERE DT.CALENDAR_DATE_NAME = '02/21/2008'

AND CD.CALL_TYPE IN (2, 15)

AND CD.DISPOSITION IN (4, 5)

GROUP BY AG.AGENT_GROUP_NAME + ' - ' + CONVERT(VARCHAR(12), AG.AGENT_GROUP_NUMBER),

AP.APPLICATION_NAME + ' - ' + CONVERT(VARCHAR(12), AP.APPLICATION_NUMBER),

AG.ADVOCATE + ' - ' + CONVERT(VARCHAR(12), AG.EXTENSION_NUMBER)

) dt

ORDER BY "Agent Group (Name & #)",

"Application (Name & #)",

"Agent (Name & #)"

This performs the arithmetic separately from the aggregation and it reads a little easier too.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ryan.quigley SSC-Addicted Points: 442 More actions · Answer 2

Thanks! Looks like I had the correct code in the first place; I just tested it incorrectly.

I do like the new structure that you proposed, so I'm going to use that.

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome Ryan. If you're not already familiar with this type of structure - the inner SELECT - then read up on derived tables. The generally accepted term for the usage here is an "onion select" which I believe was coined by Jeff Moden. It's incredibly useful and moreover the optimiser knows and likes it.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden