Create a query by checking two columns

Question

I have a following table:

tbl_mission

• id_misssao
• assignment
• status
• id_warrior
• id_creator

When the user creates a mission, he can add this mission to himself or he can indicate another user (warrior) to accomplish this mission. The logic is as follows:

• When creating a mission without indicating a warrior, creates a mission record for the user himself who is registering leaving the field id_guerreiro = NULL.
• When creating a mission indicating the warrior, create a record in the table containing the identifier of the warrior and the author of the mission.

Problem

I would like to create a query that shows the total number of missions a user has, but it is necessary to create a condition to verify that the id_guerreiro is void or not.

IF id_warrior equals NULL,
THEN returns a counter by checking the amount of items that exists for the id_criador_da_missao
OTHERWISE return a counter by checking the total amount of items in the id_guerreiro.

The following query calculates the total number of items, but only by checking the id_criador_da_missao. There is a way to check the two columns and return the total?

SELECT COUNT (id_criador_da_missao ) TOTAL FROM TBL_MISSAO

Answer 1

The following query will return the total for you, where the variable @id_usuario should be replaced by the user code you are looking for:

SELECT COUNT(1) AS total
  FROM TBL_MISSAO
 WHERE (id_guerreiro IS NULL AND id_criador_da_missao = @id_usuario)
    OR (id_guerreiro = @id_usuario)