sqlsqlitecasesql-null

SQLITE - If a column is null then the value in another column is 0

I have two columns in a table. The table name is constructed with inner join and group by, let's call this table Joined. It has two columns Present and Score. If Present is null then, I want to assign 0 to the Score value.

+------------+--------+-------------+------------+--------+
| Student_Id | Course | ExamDate    |  Present   | Score  |
+------------+--------+-------------+------------+--------+
|       1    | Math   | 04/05/2020  | Yes        | 45     |
|       2    | Math   | 04/05/2020  | NULL       | 90     |
|       2    | Math   | 04/05/2020  | NULL       | 50     |                     
+------------+--------+-------------+------------+--------+

What I have up to now is

SELECT DISTINCT StudentID ,Course, ExamDate, Present, Score
CASE Present ISNULL
Score = 0
END
    FROM Joined

I need the distinct because the inner join can give me some repetitions. What I need is

+------------+--------+-------------+------------+--------+
| Student_Id | Course | ExamDate    |  Present   | Score  |
+------------+--------+-------------+------------+--------+
|       1    | Math   | 04/05/2020  | Yes        | 45     |
|       2    | Math   | 04/05/2020  | NULL       | 0     |
+------------+--------+-------------+------------+--------+

It feels very very wrong to me but I haven't been able figure out how to do it with one query. How can I do it?

Solution

  • If Present is null then, I want to assign 0 to the Score value.

    The case expression goes like:

    select 
    present,
    case when present is null 
        then 0 
        else score 
    end as score
from ...

    You don’t tell what to do when present is not null - so this returns the original score.

    It is unclear why you would need distinct. If you were to ask a question about the original query, which seems to produce (partial) duplicates, one might be able to help fixing it.