I'm creating a SQL query with a lot of nested queries and I'm trying to use the CASE statement 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 lineupfrom teams as tjoin gameweeks as g on t.id = g.team_idjoin gameweeks_players as gp on gp.gameweek_id = g.idjoin players as p on gp.player_id = p.idjoin player_fixtures as pf on p.id = pf.player_id and pf.gw_number = g.numberwhere t.id = 1group by t.id, g.idorder by g.number ascThe 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 cptsand

( 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?
Thanks!


Check Solution