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.
| Author |
Topic |
|
sqlchiq
Posting Yak Master
133 Posts |
 Posted - 2008-10-06 : 12:24:46
|
| column12345sold6resultavg3.5I need to average a column with either rates, or 'sold' designation. I cannot do select * where column not like '%sold%'it has to be within the select statement. Ive tried the following, roomrate = case when roomrate not like '%sold%' then avg(convert(int, roomrate)) else 'hey' end,but its not working, i getMsg 245, Level 16, State 1, Line 1Conversion failed when converting the nvarchar value 'SOLD' to data type int. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 12:30:53
|
| [code]select AVG(case when roomrate like '%sold%' then 0 else roomrate *1.0 end) from yourtable[/code] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 14:05:19
|
THEN NULL ELSE 1.0E0 * RoomRate ENDBeware that zero will be included in average.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-06 : 15:17:45
|
| [code]select [Average] = convert(numeric(18,2),round( avg(case when x like '%[^0-9]%' then null else convert(numeric(18,2),x) end) ,2))from ( select x = '1' union all select x = '2' union all select x = '3' union all select x = '4' union all select x = '5' union all select x = 'sold' union all select x = '6' ) aAverage -------------------- 3.50(1 row(s) affected)Warning: Null value is eliminated by an aggregate or other SET operation.[/code]CODO ERGO SUM |
|
|
|
|
|
|
|
|
Possible to avg a column with text in it?