I have a table like follows:

------- Existing fields -------| Derive
client  Visit1  Visit2  Visit3 | CntOOS
---------------------------------------
A         OOS    OOS     RTN   |    2
B         RTN    OOS     D&D   |    1
C                RTN           |    0
D         RTN    OOS     D&D   |    1

From the given table I want to derive the count for OOS as a separate field (CntOOS) that gives me the number of times 'OOS' appeared in the above three visits.

    Requires Free Membership to View

You can do this easily with CASE expressions:

select client
     , case when Visit1 = 'OOS'
            then 1 else 0 end
     + case when Visit2 = 'OOS'
            then 1 else 0 end
     + case when Visit3 = 'OOS'
            then 1 else 0 end   as CntOOS
  from yourtable

You did not ask this, but if you need a total of CntOOS for all clients, there are two ways to do it. The first is to wrap the SUM function around the complex CntOOS expression:

select sum(
     , case when Visit1 = 'OOS'
            then 1 else 0 end
     + case when Visit2 = 'OOS'
            then 1 else 0 end
     + case when Visit3 = 'OOS'
            then 1 else 0 end
         ) as sum_CntOOS
  from yourtable

The second way is to wrap another query around your original query:

select sum(CntOOS) as sum_CntOOS
  from ( 
select client
     , case when Visit1 = 'OOS'
            then 1 else 0 end
     + case when Visit2 = 'OOS'
            then 1 else 0 end
     + case when Visit3 = 'OOS'
            then 1 else 0 end   as CntOOS
  from yourtable
       ) as details

Sometimes it's good to have alternative ways of doing something in SQL. The second method utilizes a derived table, also known as an inline view.

This was first published in August 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: