Why is this not ordering correctly? SQL

[Mutley]

SELECT t.team_id, t.level, t.name, t.initials, t.owner, l.user_id, SUM(l.2playerpts) AS 2playerpts, SUM(l.4playerpts) AS 4playerpts,SUM(l.6playerpts) AS 6playerpts, SUM(l.8playerpts) AS 8playerpts, l.wins, l.losses, u.user_id, u.team_id
		FROM teams t
		INNER JOIN users u ON u.team_id = t.team_id
		INNER JOIN leaderboard l ON l.user_id = u.user_id
		GROUP BY t.team_id
		ORDER BY (2playerpts + 4playerpts + 6playerpts + 8playerpts) DESC

 

What's happening is it's not ordering in any order, it should have the calculated row with the biggest Total SUM at the top.

 

I hope that makes sense. Thanks in advance.

[Ken2k7]

SELECT t.team_id, t.level, t.name, t.initials, t.owner, l.user_id, SUM(l.2playerpts) AS 2playerpts, SUM(l.4playerpts) AS 4playerpts,SUM(l.6playerpts) AS 6playerpts, SUM(l.8playerpts) AS 8playerpts, l.wins, l.losses, u.user_id, u.team_id
		FROM teams t
		INNER JOIN users u ON u.team_id = t.team_id
		INNER JOIN leaderboard l ON l.user_id = u.user_id
		GROUP BY t.team_id
		ORDER BY (2playerpts + 4playerpts + 6playerpts + 8playerpts) DESC

What is t.team, l.wins and u.user_id? What are all the t, l and u?

[DarkWater]

Ken, they're aliases...

[sasa]

try to change alias name from 2playerpts to something diferent

etc.

[Ken2k7]

Ken, they're aliases...

I thought you use "as" to assign aliases. ??? users u ?

[DarkWater]

Shorthand.  It works, but it's not suggested.  It's better to be verbose and use 'as' when assigning aliases.

[Mutley]

If I change the alias for the SUMs it doesn't recognize them in the ORDER BY (it thinks their a field in the actual table).

 

SELECT t.team_id, t.level, t.name, t.initials, t.owner, l.user_id, SUM(l.2playerpts) AS vr2playerpts, SUM(l.4playerpts) AS vr4playerpts,SUM(l.6playerpts) AS vr6playerpts, SUM(l.8playerpts) AS vr8playerpts, l.wins, l.losses, u.user_id, u.team_id
		FROM teams t
		INNER JOIN users u ON u.team_id = t.team_id
		INNER JOIN leaderboard l ON l.user_id = u.user_id
		GROUP BY t.team_id
		ORDER BY (2playerpts + 4playerpts + 6playerpts + 8playerpts) DESC

 

So I did it like that^ but still no luck, it's like the 2playerpts is being grabbed from somewhere else but why doesn't my SUM aliases work in there?

[sasa]

try

SELECT t.team_id, t.level, t.name, t.initials, t.owner, l.user_id, SUM(l.2playerpts) AS vr2playerpts, SUM(l.4playerpts) AS vr4playerpts,SUM(l.6playerpts) AS vr6playerpts, SUM(l.8playerpts) AS vr8playerpts, l.wins, l.losses, u.user_id, u.team_id
		FROM teams t
		INNER JOIN users u ON u.team_id = t.team_id
		INNER JOIN leaderboard l ON l.user_id = u.user_id
		GROUP BY t.team_id
		ORDER BY SUM(2playerpts + 4playerpts + 6playerpts + 8playerpts) DESC

[Mutley]

Invalid use of group function.  ???

[Mutley]

Anyone have thoughts on this problem?

 

Regards.

[richardw]

 

try this, it is somewhat of a guess,but intval may helpinthe sorting

 

 

SELECT t.team_id, t.level, t.name, t.initials, t.owner, l.user_id, SUM(l.2playerpts) AS 2playerpts, SUM(l.4playerpts) AS 4playerpts,SUM(l.6playerpts) AS 6playerpts, SUM(l.8playerpts) AS 8playerpts, l.wins, l.losses, u.user_id, u.team_id
		FROM teams t
		INNER JOIN users u ON u.team_id = t.team_id
		INNER JOIN leaderboard l ON l.user_id = u.user_id
		GROUP BY t.team_id
$sum_on_this = intval((2playerpts + 4playerpts + 6playerpts + 8playerpts));
		ORDER BY $sum_on_this  DESC	

 

 

[fenway]

Post some sample data.