how to SELECT rank

[lukep11a]

I am trying to determine the rank of a team using a single select statement. The rank is determined based on total_points.

 

SELECT COUNT(*) + 1 AS rank, SUM(CASE WHEN team_selections.transfer_in < fixtures.date AND team_selections.transfer_out > fixtures.date THEN tpts.points ELSE 0 END) as total
FROM user_teams
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points tpts
		ON team_selections.team_id = tpts.team_id
		LEFT JOIN fixtures
		ON tpts.fixture_id = fixtures.fixture_id
LEFT OUTER JOIN
(SELECT SUM(CASE WHEN team_selections.transfer_in < fixtures.date AND team_selections.transfer_out > fixtures.date THEN tpts.points ELSE 0 END) as total
FROM user_teams
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points tpts
		ON team_selections.team_id = tpts.team_id
		LEFT JOIN fixtures
		ON tpts.fixture_id = fixtures.fixture_id
		GROUP BY user_teams.user_team_id
) AS t2
     ON total < t2.total
		GROUP BY user_teams.user_team_id

 

 

The first part joins the required tables to calculate each teams points, which it does successfully. I then thought by doing a left outer join on the same select statement again I could count the number of teams that have a total that is lower, therefore returning the rank. But it isn't working, it is returning a value of rank for each team, anything between 300 and 1200 but there are only 122 teams.

 

Does anybody know if what I am trying to do is possible, or am I way off track? Any help would be greatly appreciated.