AVG on a Count not calculating properly 1

[larrydavid]

Hello, here's what I have for [Total Count] and [AVG of Total Count], but I am not seeing any difference in the results:

--TOTAL Tx
	SELECT   DATEADD(hour, DATEDIFF(hour, getdate(), t.Timestamp), getdate()) AS TxHour, 
			 COUNT(*) AS TxCount
	FROM     TEST t GROUP BY DATEDIFF(hour, getdate(), t.Timestamp)
	ORDER BY TxHour

--AVG of TxCount
SELECT TxHour, AVG(TxCount) as [AVG_CLAIM/HR] FROM 
(
	SELECT   DATEADD(hour, DATEDIFF(hour, getdate(), t.Timestamp), getdate()) AS TxHour, 
			 COUNT(*) AS TxCount
	FROM     TEST t
	GROUP BY DATEDIFF(hour, getdate(), t.Timestamp)
)x
GROUP BY TxHour
ORDER BY TxHour

I'm hoping it is just some minor thing I'm overlooking but any help would be greatly appreciated.

Thanks,
Larry

 

[gmmastros]

The inner query (the derived table) is grouping on hour, which means you will get [!]1[/!] row in the output for each hour.

The outer query is also grouping on hour, which means you will get the average of the 1 value that the inner query is returning.

try this:

--AVG of TxCount
SELECT AVG(TxCount * 1.0) as [AVG_CLAIM/HR] FROM 
(
    SELECT   DATEADD(hour, DATEDIFF(hour, getdate(), t.Timestamp), getdate()) AS TxHour, 
             COUNT(*) AS TxCount
    FROM     TEST t
    GROUP BY DATEDIFF(hour, getdate(), t.Timestamp)
)x

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[larrydavid]

Thanks George. I'm a little confused. I was trying to get the average per Hour for EACH HOUR, but if I understand correctly, what you gave me IS the average for each hour, or what would amount to the average for each hour, or is this is overall average? Sorry if I'm a bit dense on this one.

Thanks,
Larry

 

[gmmastros]

The way your original query was written....

You first calculate the count per hour. So you end up with something like this...

Hour1 0
Hour2 2
Hour3 7
Hour4 8
Hour5 20

Then, you are getting the average per hour. The average per hour would be the same because you are looking at a single number. For example, if George has 3 apples and Larry has 10 apples, what is the average number of apples that George has? The answer is 3 because you are averaging just a single value.

Can you post some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[larrydavid]

Yes, certainly. Here is what I am trying for, the hour of which is based on a timestamp field:

TxHour                         AVG of TxCount          
2011-02-17 05:48:54.633        23
2011-02-17 06:48:54.633        423
2011-02-17 07:48:54.633        234
2011-02-17 08:48:54.633        78
2011-02-17 09:48:54.633        7
2011-02-17 10:48:54.633        87
2011-02-17 11:48:54.633        33
2011-02-17 12:48:54.633        98
2011-02-17 13:48:54.633        76
2011-02-17 14:48:54.633        95
2011-02-17 15:48:54.633        127

So, I'm trying to capture the average on previous and current hourly count total based on the timestamp. Here is a sample of the table data in SQL 2005:

ID	 TxNo	         UserID	  TimeStamp
78528	60088698500	i38z872	 2011-02-17 05:56:03.180
78529	X1880731500	p69e278	 2011-02-17 05:58:49.760
78530	60088752500	i38z872	 2011-02-17 05:59:09.930
78531	60088754800	i38z872	 2011-02-17 06:01:18.807
78532	X1870960100	K071654	 2011-02-17 06:02:49.167
78533	60088755500	i38z872	 2011-02-17 06:03:33.150
78534	X1880731500	p69e278	 2011-02-17 06:03:38.010
78535	X1857707901	b151425	 2011-02-17 06:03:56.057

Thanks,
Larry

 

[gmmastros]

Based on the sample data you posted, what should the output be?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[larrydavid]

This would be the output:

TxHour AVG of TxCount
2011-02-17 05:48:54.633 23
2011-02-17 06:48:54.633 423
2011-02-17 07:48:54.633 234
2011-02-17 08:48:54.633 78
2011-02-17 09:48:54.633 7
2011-02-17 10:48:54.633 87
2011-02-17 11:48:54.633 33
2011-02-17 12:48:54.633 98
2011-02-17 13:48:54.633 76
2011-02-17 14:48:54.633 95
2011-02-17 15:48:54.633 127

Thanks,
Larry

 

[gmmastros]

It's entirely possible that I am thoroughly confused.

What I meant was.... if you just consider the 8 rows of sample data that you posted earlier, what would the output be?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[larrydavid]

I'm sorry, but I am confused by your question. The output could be many things depending on how it is queried. Can you help me to get a resultset which will return me the hourly average PER HOUR as display in the output above? I realize that this would not be possible on the first hour because at least 2 hours would be needed. Is what I'm trying to do simply not possible?