SQL Assign Custom values to those rows with similar IDs

|id|last|
|2 |NULL|
|2 |2018|
|3 |NULL|
|3 |NULL|
|4 |2011|
|4 |2013|

This is what my current table looks like. A new ‘status’ column is to be created for each ‘id’ that must have the below 3 values.

1 - If Similar id and only one NULL value
2 - If Similar id and no NULL value
0 - If Similar id and both NULL value

EXAMPLE: Id 2 will get 1, id 3 will be assigned 0 and id 4 will get 2. There can be only 2 similar ids in the id table (there are no 3 values of 2 or 4)

I could find the similar id, but having difficulties writing the cases
select id
from table
group by id
having count(id) = 2

Answer

We can determine the status values by using aggregation:

WITH cte AS (
    SELECT id,
        CASE WHEN COUNT(*) > 1 AND COUNT(CASE WHEN last IS NULL THEN 1 END) = 1
             THEN 1
             WHEN COUNT(*) > 1 AND COUNT(CASE WHEN last IS NULL THEN 1 END) = 0
             THEN 2
             WHEN COUNT(*) > 1 AND COUNT(CASE WHEN last IS NULL THEN 1 END) = COUNT(*)
             THEN 0 ELSE -1 END AS status
    FROM yourTable
    GROUP BY id
)

SELECT t1.*, t2.status
FROM yourTable t1
INNER JOIN cte t2
    ON t1.id = t2.id;

Note that I assign a status value of -1 to any id which does not meet one of the three criteria. This would include any id which only appears once, among other edge cases.