Is it possible to get an average of a count?

# Thread: Is it possible to get an average of a count?

1. Junior Member
Join Date
Dec 1969
Posts
5

## Is it possible to get an average of a count?

Hello, I am stumped and need help. I am working on a SQL Server and am using OWC to create a chart. The chart I am trying to create needs the appropriate SQL statement. I am attempting to make a chart of the average shower usage by hour of data from a specific date range. I have created a chart of the TOTAL shower usage by hour for a date range, but now I need to get the AVERAGE of the shower usage by hour. I hope this is making sense. Here&#039;s the code for the TOTAL usage:<BR><BR>objRS.Open "SELECT DatePart(hh, Busy) AS ShowerDateOnly, Count(dbo.tblShowerTransactions.TransactionID) AS CountOfTransactionID FROM dbo.tblShowerTransactions Where ShowerDate&#062;=&#039;" & StartDateID & "&#039; AND ShowerDate&#060;=&#039;" & DateAdd("d", 1, FormatDateTime(EndDateID,2)) & "&#039; AND Site IN (" & SiteID & strSelectedSites & ") GROUP BY DatePart(hh, Busy) ORDER BY DatePart(hh, Busy);"<BR><BR>I have tried to do this to get the average of the count, but it returns the same data, not averaged:<BR><BR>objRS.open "SELECT ShowerDateOnly, AVG(CountOfTransactions) AS CountOfTransactionID FROM (SELECT DatePart(hh,Busy) AS ShowerDateOnly, Count(TransactionID) AS CountOfTransactions FROM tblShowerTransactions Where ShowerDate&#062;=&#039;" & StartDateID & "&#039; AND ShowerDate&#060;=&#039;" & DateAdd("d", 1, FormatDateTime(EndDateID,2)) & "&#039; AND Site IN (" & SiteID & strSelectedSites & ") GROUP BY DatePart(hh,Busy)) tblShowerTransactions GROUP BY ShowerDateOnly ORDER BY ShowerDateOnly"<BR><BR><BR>And when I try to do something like this AVG(COUNT(dbo.tblShowerTransactions.TransactionID) ) is errors out, says I can&#039;t perform an aggregate on an aggregate. Does anyone have any idea how to get an average of a count on a SQL Server? Or am I going about this totally wrong? I&#039;m losing my patience over this, uggg! TIA!<BR><BR>Rebekah

2. Senior Member
Join Date
Dec 1969
Posts
549

## RE: Is it possible to get an average of a count?

I could be barking up the wrong tree but maybe you need to do something like:<BR><BR>objRS.Open "SELECT DatePart(hh, Busy) AS ShowerDateOnly, Count(dbo.tblShowerTransactions.TransactionID)/Count(DatePart(hh, Busy)) AS Average FROM dbo.tblShowerTransactions Where ShowerDate&#062;=&#039;" & StartDateID & "&#039; AND ShowerDate&#060;=&#039;" & DateAdd("d", 1, FormatDateTime(EndDateID,2)) & "&#039; AND Site IN (" & SiteID & strSelectedSites & ") GROUP BY DatePart(hh, Busy) ORDER BY DatePart(hh, Busy);"<BR>

3. Junior Member
Join Date
Dec 1969
Posts
5

## RE: Is it possible to get an average of a count?

Thank you Jerome! It wasn&#039;t exactly what I was looking for, but it got my brain started! I was so focused on using the built in AVG function, I forgot about plain old math! I divided the Count by the date difference, and voila! It works! Thanks for your help! =)<BR><BR>Rebekah

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•