Computing different sums depending on the value of one column
In psuedo-code, this is bascially what I would like to do:
select
if a1 >= 0 and a1 <= 2
a2 + a7 + a8 as sum
else
a2 + a8 as sum
from table_name
;
(The column names, etc. are just for illustration.)
In English: I 开发者_StackOverflow社区need to sum different columns for each of a few thousand records. I'd like to do the sum in SQL, if possible. There are only one or two differences between the conditions, either a single column missing/added, a substitution, or both. This is based on the business logic of the application, not a design decision of my choice.
I'm using sqlite3 (3.6.10), which limits the options somewhat.
Here you can use a trick that boolean expressions evaluate to either 0 or 1 in SQL:
SELECT a2 + a8 + a7 * (a1 BETWEEN 0 AND 2) AS SUM
FROM table_name
A more general (and more conventional) way is to use a CASE expression:
SELECT
CASE WHEN a1 BETWEEN 0 AND 2
THEN a2 + a7 + a8
ELSE a2 + a8
END AS SUM
FROM table_name
You can also do something like this to include a CASE expression without repeating the common terms:
SELECT
a2 + a8 + (CASE WHEN a1 BETWEEN 0 AND 2 THEN a7 ELSE 0 END) AS SUM
FROM table_name
精彩评论