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.
Requires Free Membership to View
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 first published in November 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation