Case expression does not return the "else" value in Postgres query

Question

I'm creating a SQL query with a lot of nested queries and I'm trying to use the CASE expression but it is behaving weirdly.

This is my query at the moment:

select t.fpl_id, t.team_name,
sum(pf.points)as gwpts,
(
    select sum(transfers_malus)
    from gameweeks
    where gameweeks.team_id = t.id and gameweeks.number = g.number
)as malus,
(
    select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
)
as cpts,
 (
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,
array_agg(p.id) as lineup
from teams as t
join gameweeks as g on t.id  = g.team_id
join gameweeks_players as gp on gp.gameweek_id = g.id
join players as p on gp.player_id = p.id
join player_fixtures as pf on p.id = pf.player_id  and pf.gw_number = g.number
where t.id = 1
group by t.id, g.id
order by g.number asc

The bit where I have a problem is when I'm doing the case statement to see if one of the player ( the captain ) hasn't played based on "minutes_played":

(select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as cpts

and

 (
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,

When minutes_played is indeed greater than 0, everything works fine. However, when it is equal to 0 it just doesn't "return" anything and I get a NULL in return in my row. minutes_played is an integer and when I try with the following script on the player_fixtures where minutes_played is = 0 my > evaluation works and I get the correct "BAD".

DO LANGUAGE plpgsql $$
    BEGIN
        IF (select minutes_played from player_fixtures where gw_number = 19 and player_id = 266 ) > 0 THEN 
            RAISE NOTICE 'GOOD';
        ELSE
            RAISE NOTICE 'BAD';
        END IF;
    END;
$$;

I'm pretty new at this whole DB thing so I'm probably making a rookie mistake but I've been trying for the past 6hours without any luck. Could someone point me in the right direction?

What happens if you use alias in your case statement, like e.g. for the first one: `CASE WHEN pfix.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END` — cha, Jan 02 '14 at 23:50
@cha already tried with alias everywhere or no alias, didn't work either :( — Syl, Jan 03 '14 at 11:19

score 0 · Answer 1 · answered Jan 03 '14 at 00:58

The problem is that when there are no rows that match, sum() returns a null, and no comparison with null is ever true.

Instead, use exists(), for example:

where exists(select * from player_fixtures
    where gw_number = 19
    and player_id = 266 
    and minutes_played > 0)

or you can use if exists(...) then in a stored proc.

Case expression does not return the "else" value in Postgres query

1 Answers1