Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use aliases in math operators in SQL?

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 !

like image 827
jeff Avatar asked Jan 26 '14 19:01

jeff


People also ask

How do you use an alias in SQL?

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.

Can alias be used in expression in SQL?

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.

What operator would I use to create an alias in a query?

SQL AS keyword is used to give an alias to table or column names in the queries.


2 Answers

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
like image 186
jbeldock Avatar answered Oct 17 '22 08:10

jbeldock


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.

like image 23
MrVimes Avatar answered Oct 17 '22 07:10

MrVimes