Unknown column (assigning using AS)

SELECT 
  u.user_id, 
  u.user_name, 
  (
    SELECT COUNT(friend_from) 
    FROM friends 
    WHERE friend_from = u.user_id
  ) AS friends_added,
  (
    SELECT COUNT(user_referered) 
    FROM users 
    WHERE user_referered = u.user_id
  ) AS friends_referered, 
  friends_added + (friends_referered * 10) AS points
  FROM users u

Code (markup):

Can anyone explain why the following is giving me the following error:

If anyone can help me correct this query, that would be great.

Cheers.

 

Depending on what database program you are sing, it may not support the use of aliases the way you are in the select. It is one thing to give a field an alias - the 'AS friends_added' part. It is totally different to use the alias later in the select - the 'friends + (fri...' part. Some databases allow this (MSSQL) and some don't.

There are three choices.

1) If you can do stored procedures, then you can declare friends_added as a variable (using the @ symbol) and set the value before your main select statement.

2) You can replace: friends_added + (friends_referered * 10) AS points
with:
(SELECT COUNT(friend_from) FROM friends WHERE friend_from = u.user_id) + (SELECT COUNT(user_referered) FROM users WHERE user_referered = u.user_id) AS points

3) Because points is a derived field, you could do the calculations on the page (or form) since the query returns the two numbers required to make the calculation.

If this is what is occuring, then the database will probably see friends_referered * 10 as an error too.