Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3720
  • Last Modified:

Convert Sum(IIf statement) to T-SQL

I have an Access SQL query I'd like to convert to a stored procedure in SQL Server 7.  I'm pretty sure it requires a case when statement, but I'm stuck!

How can I translate this statement to t-sql?

Sum(IIf(IsNull(table.field),1,0)) as SumOfEmptyField

Thanks much!
0
tradenut
Asked:
tradenut
  • 3
  • 2
1 Solution
 
tchalkovCommented:
create a view , which has the case statement, and then a stored procedute which uses this view in stead of the table
0
 
tradenutAuthor Commented:
my main problem is wording the case statement.
Can you give a small example?
0
 
tchalkovCommented:
select t=case type
               when 1 then 'OK'
               when 2 then 'Not OK'
         else 'Unknown'
         end

there are a lot of examples in books online - the help of SQL Server
tell me what exactly what you want to do if you want more specific help

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
simonsabinCommented:
Sorry to steal the thunder but here you go

Sum(CASE WHEN table.field IS NULL THEN 1 ELSE 0 END)
 
0
 
tchalkovCommented:
its ok simonsabin
you have the correct answer
0
 
tradenutAuthor Commented:
Thanks to you both for the help.

Wish I could distribute points..  =)
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now