🚀 go-pugleaf

Hi.

I have a problematic query which  cant get right. The query builds a league
table of team names and respective points. Could someone tell me the correct
way to add the penalty information on to the points totals for each team?

original query >>

SELECT v.Name AS Team, Sum(r.Points) AS TeamTotal
FROM VF1Team AS v, TeamPicks AS t, Driver AS d, RaceResults AS r, venue
WHERE (((v.ID)=[t].[teamid]) AND ((t.driverID)=[d].[id]) AND
((d.ID)=[r].[driverid]) AND ((r.VenueID)=[venue].[id]))
GROUP BY v.Name
ORDER BY Sum(r.Points) DESC;

---

attempt 1
========

SELECT v.Name AS Team, Sum(r.points + (SELECT vf1team.Penalty from vf1team
where vf1team.penalty is not null))
FROM VF1Team AS v, TeamPicks AS t, Driver AS d, RaceResults AS r, venue
WHERE (((v.ID)=[t].[teamid]) AND ((t.driverID)=[d].[id]) AND
((d.ID)=[r].[driverid]) AND ((r.VenueID)=[venue].[id]))
GROUP BY v.Name
ORDER BY Sum(r.Points) DESC;

attempt 2
=======

SELECT v.Name AS Team, Sum(r.points + V.Penalty)
FROM VF1Team AS v, TeamPicks AS t, Driver AS d, RaceResults AS r, venue
WHERE (((v.ID)=[t].[teamid]) AND ((t.driverID)=[d].[id]) AND
((d.ID)=[r].[driverid]) AND ((r.VenueID)=[venue].[id]))
GROUP BY v.Name
ORDER BY Sum(r.Points) DESC;