Totalling higher of two columns by row.

Assume that I have a table containing two fields, Total and Total2. I need the sum of whichever is higher for each row, for example, with the following sample data:



The query should return 15(3+2+10).

Any good ideas on how to do this simply?

1) That isn't good database design.
2) In order to return the information you need, you would need to write a Case statement to determine which value in each row is higher, then add those together.

I know it isn't exactly good database design, but those fields are part of the results of another query, each containing the sum of a column on another table, generated off of some much more meaningful data. Generally the second total is the one to use, but there are some old legacy entries in the table from before the second total existed, and they need to be accounted for.

I've been working on the system this is part of for a while, and I keep getting requests for additional features to be added to the system, which need to be wedged in via the most efficient route available. This means that some parts of the system are not exactly the most attractive or well designed pieces of code, since they were originally intended to do about half of the things they currently do.

Frankly, if I knew it had to do what it does now (nm what it will do in a week), I would have designed it completely differently from the beginning (Hell, the DB would even be completely in 4th normal form, but needing to arbitrarily pull fields/tables in and out because the people you're coding for change their requirements daily makes it less reasonable to guarantee things stay that way).

In fact, ATM I'm revising some display pages and throwing out a lot of old, very ugly, very kludgy code. Was hoping there was a more elegant way to do that total than to loop through the recordset and add it up as I went, but apparently that's as clean as it can get.