-2

I have the following code in Access and I need it to work in sql, the Is numeric portion is throwing me off.

Sum([Employee Count]*IIf(IsNumeric([Length]),[Length],0)) AS Total_hours,
user1958651
  • 451
  • 1
  • 5
  • 8
  • 2
    For which DBMS do you want this? SQL is just a *language* it is *not* a DBMS product. – a_horse_with_no_name Jan 21 '13 at 20:59
  • I can never tell why people throw negatives on posts... I guess you didn't show what you've tried already. No? Bunch of nazis here. If you're downvoting.. might be helpful if you explained yourself a little. In the name of progress. – sam yi Jan 22 '13 at 16:27

2 Answers2

2

You will replace the IIF() with a CASE expression. The IsNumeric() is valid in SQL Server:

Sum([Employee Count]*
    case when IsNumeric([Length]) = 1
          then [Length]
          else 0 end) AS Total_hours,
Aaron Bertrand
  • 261,961
  • 36
  • 448
  • 471
Taryn
  • 234,956
  • 54
  • 359
  • 399
0

You can also just filter "not isnumeric" out since 0 will not impact SUM aggregation.

select
Sum([Employee Count]*[Length]) AS Total_hours
...
where isnumeric([Length]) = 1

See code below.

declare @table table (abc varchar(100))
insert into @table
select '1'
union select '200'
union select '200A'

select sum(convert(int,abc))
from @table
where isnumeric(abc) = 1

sqlfiddle

sam yi
  • 4,689
  • 1
  • 27
  • 39