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 !
SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.
An SQL alias is useful for simplifying your queries and making the query and its result more readable. This article explains why and how you can use aliases in your SQL queries. You can temporarily rename a table or a column by giving it another name.
SQL AS keyword is used to give an alias to table or column names in the queries.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With