SQL Brain-Freeze - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Brain-Freeze

Author

Topic

qdaddyo
Starting Member

7 Posts

Posted - 2008-02-28 : 20:39:20

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 & #)"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 00:10:59

What does the SUM() and COUNT() values are, separately?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qdaddyo
Starting Member

7 Posts

Posted - 2008-02-29 : 02:31:02

Here is some data:

Total Calls Average Talk Average Hold Average Wrapup Average AHT
2 423 0 210 633
3 1223 725 25 1974

As you can see, there is no division occurring. I want the "Average Talk" to be, in the case of the first row of data, 423/2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources