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.

This Content Component encountered an error

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close