Solved

Convert Sum(IIf statement) to T-SQL

Posted on 2000-05-09
6
3,706 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now