CODEWITHSUNDEEP
sqlpostgresql
Alexey Bille
Alexey Bille

Reputation: 3

How to add column based on dynamic column

An example of a simple one to simplify understanding of a question, in a real query it will be difficult to simply write "value + 20".

Example table

id | value |
-------------
 1 |   20  |
 2 |   30  |

Query example:

SELECT id, value, value + 10 as value1, value1 + 10 as value2
FROM table;

Error: ERROR: column "value1" does not exist

Is there any way I can make this query work?

Upvotes: 0

Views: 115

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You cannot use aliases defined in the select again in the same select, where, or from clauses (and many databases extend this to group by and having).

This is rule in SQL. You may wonder why, but here is a simple explanation for the select. Remember that SQL is defined around sets, which have no order. SQL simply does not guarantee the order of evaluation of expressions in the select. Without that guarantee, the engine doesn't know which is going to be evaluated first.

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You can't use an alias defined in a SELECT clause in the same clause. So, just repeat your logic:

SELECT
    id,
    value,
    value + 10 AS value1,
    value + 20 AS value2
FROM table;

I don't know if this query represents your actual problem, or if it be a simplification. If you had a complex expression in a select, and you really did not want to repeat it, then you could subquery:

WITH cte AS (
    SELECT id, value, value + 10 AS value1
    FROM table
)

SELECT *, value1 + 10 AS value2
FROM cte;

Upvotes: 1

Related Questions