AVG With DATEDIFF - 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 2008 Forums

Transact-SQL (2008)

AVG With DATEDIFF

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2014-06-16 : 10:09:00

I'm getting some odd results with trying to find the average of the number of minutes between two date values.

Here is a list of records showing the number of minutes between two values which is correct.

select top 10
DATEDIFF(minute, timeofreview, endtimeofreview) AS CallMins
from calls

But what I want is the average of these ten and this produces the results below.

select top 10
AVG(DATEDIFF(minute, timeofreview, endtimeofreview)) as AVGDiff
from calls

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-16 : 10:43:43

with ctesource as (select top(10) datediff(minute, timeofreview, endtimeofreview) as avgdiff)
select avg(avgdiff) from ctesource

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

evanburen
Posting Yak Master

167 Posts

Posted - 2014-06-16 : 10:51:37

Thanks but I'm getting back this

Invalid column name 'timeofreview'
Invalid column name 'endtimeofreview'

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-16 : 11:26:38

Check your column names?

We are the creators of our own reality!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-16 : 11:35:39

There is no table name on the select in the CTE.


with ctesource as (select top(10) datediff(minute, timeofreview, endtimeofreview) as avgdiff from calls)
select avg(avgdiff) from ctesource

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-16 : 11:38:51

Yep missing From

We are the creators of our own reality!

evanburen
Posting Yak Master

167 Posts

Posted - 2014-06-16 : 13:26:25

working now. thanks a lot.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-16 : 13:33:04

[code]

SELECT TOP (10)
CAST(SUM(DATEDIFF(SECOND, timeofreview, endtimeofreview)) / 60.0 / COUNT(*) AS decimal(5, 1)) AS AVGDiff
FROM calls

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources