Third column conditional upon first two columns (SQL)

Question

In DB Browser for SQLite, if I have two columns, results1 and results2, each of which can either have "pass" or "fail" (or blank( in the column, how can I create a third column called results_final which returns "pass" if both results1 and results2 had "pass" in the same row, or returns "fail" if it was any other combo? So essentially trying to use a CASE WHEN but with conditions in two different columns. Is this possible? In Excel, I could just do a =IF(AND(pass,pass), "pass", "fail") sort of formula but what can I do here?

Answer 1

You can use a case expression like so:

select 
    t.*, 
    case when result1 = 'pass' and result2 = 'pass' 
        then 'pass' 
        else 'fail' 
    end result3
from mytable t

It is unclear whether you actually want to create a new column to hold that value; I wouldn't actually recommend storing that information. You can use a view, or a generated virtual column for that:

alter table add column column3 text as (
    case when result1 = 'pass' and result2 = 'pass' 
        then 'pass' 
        else 'fail' 
    end) virtual