Q
Problem solve Get help with specific problems with your technologies, process and projects.

Conditionally summing fields

How would one go about conditionally summing fields based on other fields? For example,

TABLE1: MYDATE, ROUTE, CODE1, TIME1, CODE2, TIME2

TABLE2: ROUTE, DEST

TABLE3: CODE, TOWER(YES OR NO TYPE)

I need a statement to give me a data set with the following fields: MYDATE, DEST, SUM of all TIMEs in TABLE1 with a corresponding CODE in TABLE1 that is a YES in the TOWER field in TABLE3.


In order to find corresponding CODE values for both CODE1 and CODE2, TABLE1 needs to be joined to two copies of TABLE3. (Actually, the database engine does not necessarily use two copies, but it helps to think of it that way.) The two copies of TABLE3 are distinguished in the query by table aliases t31 and t32:

select t1.mydate
     , t2.dest
     , case
         when t31.tower is not null
          and t31.tower = 'Y'
         then t1.time1 
         else 0
       end 
     + case
         when t32.tower is not null
          and t32.tower = 'Y'
         then t1.time2 
         else 0
       end 
  from table1 t1
inner
  join table2 t2
    on t1.route = t2.route
left outer
  join table3 t31
    on t1.code1 = t31.code
left outer
  join table3 t32
    on t1.code2 = t32.code

The conditional sum is obtained by adding together the values of two CASE expressions. Each CASE handles a TOWER value from one of the two left outer joins, and if CODE1 and CODE2 are different, then the TOWER values will have come from separate rows of TABLE3 (since both copies of TABLE3 came from the same source TABLE3, as it were).

Each CASE expression starts with an IS NOT NULL check, to ensure that the CODE1 or CODE2 value had a CODE match in TABLE3. This is because in a left outer join, if a row isn't matched, then the columns from the table on the right of the join will be NULL. If there is a match, then TOWER will be NOT NULL, so TOWER is then tested equal to 'Y' (or 'YES' or whatever). If it meets this condition too, then the CASE expression will have a value equal to TIME1 or TIME2, as given by the THEN clause. If there was no join match on CODE, then TOWER will be NULL, and so the CASE expression will have a zero value, as given by the ELSE clause.

If you are certain that TABLE1's code values will both always be found in TABLE3, then you could use inner joins instead of outer. My preference is for outer joins, because that way you're covered no matter what's in the tables. I have occasionally uncovered data integrity problems by using left outer joins in situations where I was told "Of course all the keys match." You could call this defensive SQL.

So why did the query use an inner join to TABLE2? My deliberately unspoken assumption here was that TABLE2 keys would always have a match. The challenge in this problem was to use left outer joins to two copies of TABLE3. Adding another outer join into the discussion could have been confusing. This said, I would use a left outer join to TABLE2 as well.


This was last published in November 2002

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