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 !

Column alias available only after the `SELECT` is processed. So Only the `ORDER BY` has access. In your case, you may need to subquery your `COUNT()` values with an alias, and later refer in the outer part. — Maheswaran Ravisankar, Jan 26 '14 at 19:20
similar question answered [here](https://stackoverflow.com/a/18020948/382515) — Ivan Ferrer Villa, Sep 26 '18 at 14:33

score 10 · Answer 1 · answered Jan 26 '14 at 19:24

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

MrVimes · Accepted Answer · 2016-02-29T18:53:32.983

5

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.

edited Feb 29 '16 at 18:53

answered Jan 26 '14 at 19:18

MrVimes

3,212
10
39
57

Will there be any difference in performance between the approaches suggested above and by @jbeldock? – Supratim Haldar Nov 08 '19 at 03:42

score 1 · Answer 3 · answered Jan 26 '14 at 19:54

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;

How to use aliases in math operators in SQL?

3 Answers3

Linked