Return 1 instead of 0 when Count(*) result is Null

Question

My code from SQL Server:

SELECT ESTAGIO.SK_ESTAGIO, ISNULL(count(ESTAGIO.SK_ESTAGIO), 0) as how_many
 from ESTAGIO
 left join ESTAGIARIO
 on ESTAGIARIO.SK_ESTAGIO = ESTAGIO.SK_ESTAGIO
 group by
    ESTAGIO.SK_ESTAGIO

When "ESTAGIO.SK_ESTAGIO" doesn't exist in the table "ESTAGIARIO" it returns 1 instead of 0, I already tried to use ISNULL(), NULLIF() and COALESCE() and still couldn't find the problem that is making the query above returning 1 when it should be 0.

Answer 1

You are counting the wrong field. Do it like this, taking the field from the outer joined table ESTAGIARIO (not from ESTAGIO):

SELECT ESTAGIO.SK_ESTAGIO, Count(ESTAGIARIO.SK_ESTAGIO) as how_many
 from ESTAGIO
 left join ESTAGIARIO
 on ESTAGIARIO.SK_ESTAGIO = ESTAGIO.SK_ESTAGIO
 group by
    ESTAGIO.SK_ESTAGIO

BTW, count can never return null.