Min, Max and Avg ? - 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

General SQL Server Forums

New to SQL Server Programming

Min, Max and Avg ?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-10-07 : 03:08:52

I have a table with three fields,
Name - varchar(200)
run_requested_date - datetime
Duration - varchar(12)

I need to get the Min, Max and Avg Duration, the Min and Max seems to work ok but I'm having problems with the Avg and get the following message:-
'Operand data type varchar is invalid for avg operator'

I've tried converting it but am still getting probs, any ideas

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 03:22:01

Avg() function support only int,float,money,smallmoney,decimal datatypes

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-10-07 : 03:26:59

Why does this work then?

DECLARE @Table TABLE (start datetime, stop datetime)

INSERT INTO @Table
SELECT '9/2/09 12:42', '9/2/09 17:00' UNION ALL
SELECT '9/2/09 14:57', '9/2/09 15:00' UNION ALL
SELECT '9/2/09 15:02', '9/2/09 16:05' UNION ALL
SELECT '9/2/09 16:18', '9/2/09 18:28'

select [Avg] = convert(varchar(10),avg(datediff(n,start,stop))/60)
+':'+ convert(varchar(10),avg(datediff(n,start,stop))%60)
,[MaxDiff] = convert(varchar(10),max(datediff(n,start,stop)) /60) +':'
+ convert(varchar(10), max(datediff(n,start,stop)) %60)
from @Table

I found the code on this site.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 03:31:53

quote:
Originally posted by Bill_C

Why does this work then?

DECLARE @Table TABLE (start datetime, stop datetime)

INSERT INTO @Table
SELECT '9/2/09 12:42', '9/2/09 17:00' UNION ALL
SELECT '9/2/09 14:57', '9/2/09 15:00' UNION ALL
SELECT '9/2/09 15:02', '9/2/09 16:05' UNION ALL
SELECT '9/2/09 16:18', '9/2/09 18:28'

select [Avg] = convert(varchar(10),avg(datediff(n,start,stop))/60)
+':'+ convert(varchar(10),avg(datediff(n,start,stop))%60)
,[MaxDiff] = convert(varchar(10),max(datediff(n,start,stop)) /60) +':'
+ convert(varchar(10), max(datediff(n,start,stop)) %60)
from @Table

I found the code on this site.

Look here in the above case you put avg() on datediff()%60,
datediff will return int value,then you put % operator,still the value is int.

Its clear you pass int value to avg ()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-10-07 : 04:07:15

OK, Thanks.
I will look at a solution based around the above

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 04:16:46

quote:
Originally posted by Bill_C

OK, Thanks.
I will look at a solution based around the above

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources