Averages of columns in a table with SQL

[dlpastel]

In reference to the chart below:

Date Callers Times Called
09/21/2009 2 1
09/21/2009 3 2
09/21/2009 1 3
09/22/2009 1 10
09/22/2009 2 3
09/22/2009 3 4

I am trying to get an average number of the two above columns. I am using the code below. The problem seems to be the "GROUP BY StartDate" portion of the sql. I am getting averages grouped by date and I am looking an average for the entire date range.
I cannot take out the "GROUP BY StartDate" clause without getting an error.

Sub RptReturn_GT()
Dim E As Integer, RptDays As Integer, CallerCount As Integer, TimesCalled As Integer, AvgCallerCount As Integer, AvgReturnCalls As Integer
Dim StrSql As String
StrSql = "SELECT COUNT(*) AS CallerCount, Sum(CallCount) as TimesCalled " & _
"From Clients " & _
"GROUP BY StartDate, MemberType, CallCount " &
"HAVING StartDate >= '" & StartDate & "' " & _
"And StartDate <= '" & EndDate & " ' " & _
" AND (MemberType = N'FreeLine') "


CreateRecordSet rsStats, StrSql

E = FreeFile

Open FilePath & FileName For Append As E

With rsStats
RptDays = DateDiff("d", StartDate, EndDate)
AvgCallerCount = !CallerCount / RptDays
AvgReturnCalls = !TimesCalled / RptDays


Print #E, "<TD width='11%' height=26><DIV align=left><FONT face='Arial, Helvetica, sans-serif'"
Print #E, "size=2><b><i>Totals:</i></b></FONT></DIV></TD>"
Print #E, "<TD width='17%' height=26><DIV align=right><FONT face='Arial, Helvetica, sans-serif'"
Print #E, "size=2>" & AvgCallerCount & "</FONT></DIV></TD>"
Print #E, "<TD width='20%' height=26><DIV align=right><FONT face='Arial, Helvetica, sans-serif'"
Print #E, "size=2> " & TimesCalled & " </FONT></DIV></TD>"
Print #E, "</TR>"
Print #E, "</TABLE>"
End With
Close #E
End Sub

Thanks in advance!

 

[HarleyQuinn]

Could you show us some more example data please? For example, what is CallCount (I could hazard a guess but do you really need to group on it)? Why do you need to GROUP BY MemberType when you specify it in the HAVING clause? Do you need a HAVING clause at all (when a WHERE clause could probably fit in this example better)?

I take it the chart at the top is the output of your query (though with different columns and names)?

If you just want two numbers returned then you could try:

StrSql = "SELECT COUNT(*) AS CallerCount, Sum(CallCount) as TimesCalled " & _
             "From Clients " & _
             "[red]WHERE[/red] StartDate >= '" & StartDate & "' " & _
             "And StartDate <= '" & EndDate & " ' " & _
             " AND (MemberType = [blue]N'FreeLine'[/blue]) "

I've highlighted the bit in [blue]blue[/blue] as I assume it's a typo but your query will error out if you don't correct it.

I've also changed the HAVING clause to a WHERE clause as I've removed your GROUP BY (which you need for a HAVING clause).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post