How to use aliases in math operators in SQL?

Question

First of all, please correct me if "alias" is the wrong word. I am talkin about renaming the column with AS operator.

So I'm trying calculate an average like this :

SELECT
users.username AS player_name,
COUNT(*) AS total_games,
SUM(games.points) AS total_points,
(total_points / total_games) AS average_points

FROM games,
INNER JOIN users
ON games.player_id = users.id
GROUP BY games.player_id

(the query might be wrong, its just a quick example)

in this query, the line

(total_points / total_games) AS average_points

gives an error : unknown column total_points

so how can I fix this to keep using the aliases, instead of writing this :

(SUM(games.points) / COUNT(*) ) AS average_points

Thanks for any help !

Solution 1

I'm fairly sure it's not possible to use aliases like that. You will have to do it the 'long' way...

(SUM(games.points) / COUNT(*) ) AS average_points

Edit: I wanted to edit this answer to second @jbeldock 's answer as a solution I have since used many times myself. I found myself writing out formulas and re-using them in other parts of queries, leading to big messy queries with copy/pasted sections. Putting your forumlas in a subquery allows you to use their results in the outer query and make things more elegant.

Solution 2

Unfortunately, you cannot use column aliases this way. They are only available after the SELECT statement is processed. However, you can do it in a subquery or a common table expression (CTE). Here is the simple subquery:

SELECT player_name,
       total_games,
       total_points,
       (total_points / total_games) AS average_points
FROM
 (SELECT
 users.username AS player_name,
 COUNT(*) AS total_games,
 SUM(games.points) AS total_points,

 FROM games,
 INNER JOIN users
 ON games.player_id = users.id
 GROUP BY games.player_id) as InnerQuery

Solution 3

You can't use aliases at the same level they are defined -- you need to put the alias definition in a subquery. But, you don't need aliases, because SQL has the AVG() function:

SELECT users.username AS player_name,
       COUNT(*) AS total_games,
       SUM(games.points) AS total_points,
       AVG(games.points) AS average_points
FROM games INNER JOIN
     users
     ON games.player_id = users.id
GROUP BY games.player_id;

Comments

[Supratim Haldar]

Will there be any difference in performance between the approaches suggested above and by @jbeldock?