Two ways to SUM a complex expression

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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top