SQL Query Question

[derek7467]

Greetings. I have some SQL query i wrote. It outputs correctly. Gives me a per agent look at different stats. im looking to change this SQL code to average the results. 1 Row where it averages each column for that particular day.

Can anyone help? Ive tried many different combinations and none seem to work, i keep getting expression errors: (This code currently works as intended - I want to average it now)

SELECT [qAvaya by Day by Agent].LoginDate, [qAvaya by Day by Agent].SumOfACDCalls AS [ACD Calls], Format((Avg([AvgACDTime])/86400),"hh:nn:ss") AS [Avg ACD], Format((([qAvaya by Day by Agent]![SumOfACWTime])/86400),"hh:nn:ss") AS [Total ACW], Format(((([qAvaya by Day by Agent]![SumOfACWTime]+[qAvaya by Day by Agent]![SumOfACDTime])/[qAvaya by Day by Agent]![SumOfACDCalls])/86400),"hh:nn:ss") AS AHT, (([qAvaya by Day by Agent]![SumOfACDCalls])/(([qAvaya by Day by Agent]![SumOfStaffedTime]-[qAvaya by Day by Agent]![SumOfLunchTime]-[qAvaya by Day by Agent]![SumOfBreakTime]-[qAvaya by Day by Agent]![SumOfAvailTime])/3600))/7.38 AS AgentCPH
FROM (EmployeeList INNER JOIN [qAvaya by Day by Agent] ON EmployeeList.AvayaID = [qAvaya by Day by Agent].AvayaID) INNER JOIN AIDailyCallLogBySkill ON EmployeeList.AvayaID = AIDailyCallLogBySkill.AvayaID


GROUP BY [qAvaya by Day by Agent].LoginDate, [qAvaya by Day by Agent].SumOfACDCalls, Format((([qAvaya by Day by Agent]![SumOfACWTime])/86400),"hh:nn:ss"), Format(((([qAvaya by Day by Agent]![SumOfACWTime]+[qAvaya by Day by Agent]![SumOfACDTime])/[qAvaya by Day by Agent]![SumOfACDCalls])/86400),"hh:nn:ss"), (([qAvaya by Day by Agent]![SumOfACDCalls])/(([qAvaya by Day by Agent]![SumOfStaffedTime]-[qAvaya by Day by Agent]![SumOfLunchTime]-[qAvaya by Day by Agent]![SumOfBreakTime]-[qAvaya by Day by Agent]![SumOfAvailTime])/3600))/7.38
HAVING ((([qAvaya by Day by Agent].LoginDate)=#2/14/2014#) AND (([qAvaya by Day by Agent].SumOfACDCalls)>0));

[rpeare]

Any chance you could upload a sample database with some garbage data in it for analysis, hard to tell where the problem lies without something to go with it.

Also what is the calculation you are trying to perform if the SQL code you pasted 'works'

[derek7467]

This query gives me a row of Help Desk agents and each other row realtes to a metric theyre scored on. What i am trying to do is take the total of each metric row and average, therefore i can evaluate the agent against the average.

[Xipooo]

Drop your grouped by statement and wrap each SELECT field with an avg function, then be sure to use the "as" statement to give your column a more meaningful name.

Like this: avg([qAvaya by Day by Agent].LoginDate) as [Average Login Date]

[rpeare]

Don't understand your jargon sorry.

if each row represents a different agent and you want to perform a calculation, let's say your column ACD calls, where you want to total the calls across all agents, then show which agent controlled which percent of the total calls you do not want to do that in a query. That is an operation you should be performing on a report. Trying to perform it in a query is extremely memory intensive and will slow down your query immensely as your database gets larger. If you are exporting this data to an excel file you can put that in as well but in a query is the wrong place to do it.

[derek7467]

Xipooo, i did wrap it with the avg aggregrate function and received the "expression is too complex to evaluate":

SELECT Avg(SupervisorDailyHuddle.[ACD Calls]) AS [AvgOfACD Calls], Avg(SupervisorDailyHuddle.[Avg ACD]) AS [AvgOfAvg ACD], Avg(SupervisorDailyHuddle.[Total ACW]) AS [AvgOfTotal ACW], Avg(SupervisorDailyHuddle.AHT) AS AvgOfAHT, Avg(SupervisorDailyHuddle.AgentCPH) AS AvgOfAgentCPH
FROM SupervisorDailyHuddle;

[Xipooo]

Then some of the fields cannot be averaged. Figure out which ones and either do a different aggregate function or put that field in your group by section.

[derek7467]

i was using sub queries under sub queries. I went back and used the raw data and made new queries and it worked.

[rpeare]

so much easier with an example db to work with!