Solved

Convert Sum(IIf statement) to T-SQL

Posted on 2000-05-09
6
3,710 Views
Last Modified: 2008-11-20
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
Comment
Question by:tradenut
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:tchalkov
ID: 2792771
create a view , which has the case statement, and then a stored procedute which uses this view in stead of the table
0
 

Author Comment

by:tradenut
ID: 2792809
my main problem is wording the case statement.
Can you give a small example?
0
 
LVL 7

Expert Comment

by:tchalkov
ID: 2792829
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 7

Accepted Solution

by:
simonsabin earned 50 total points
ID: 2792834
Sorry to steal the thunder but here you go

Sum(CASE WHEN table.field IS NULL THEN 1 ELSE 0 END)
 
0
 
LVL 7

Expert Comment

by:tchalkov
ID: 2792867
its ok simonsabin
you have the correct answer
0
 

Author Comment

by:tradenut
ID: 2793499
Thanks to you both for the help.

Wish I could distribute points..  =)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question