I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck...
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
he 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 pfix.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,
My expected result would be that if minutes_played is greater than 0 then return the row that matches gw_number = g.number and player_id = g.captain_id, otherwise return the row that matches gw_number = g.number and player_id = g.vice_captain_id
When minutes_played is indeed greater than 0, everything works fine. However, when it is equal to 0 it doesn't return any row and thus I get a NULL in the row of my overall query.
The funny thing is that if I try with the following script on the player_fixtures where minutes_played = 0 my greater than 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 after trying to solve it myself for 6hours, I think it is time I ask for help. Could someone point me in the right direction?
Thanks!
EDIT with more info following @Mihai answer:
when I cover the 0 condition it still return 0 row. I tried with the following test data:
select *
from player_fixtures
where gw_number = 19 and player_id = case when player_fixtures.minutes_played > 0 then 266 when player_fixtures.minutes_played = 0 then 214 end
If I run
select *
from player_fixtures
where gw_number = 19 and player_id in (266,214)
I get 2 rows with the correct data so there must be a problem when I do the case statement...
minutes_played IS NULL