Q

Two ways to SUM a complex expression

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.

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.

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 last published in August 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close