How Can I Use Calculated Columns from the Same Query in PostgreSQL?

Using computed columns in the same query in PostgreSQL

PostgreSQL does not allow calculations using aliases directly within the same SELECT statement. For example, the following SQL statement will report an error:

<code class="language-sql">SELECT cost_1, quantity_1, cost_2, quantity_2, 
(cost_1 * quantity_1) AS total_1,
(cost_2 * quantity_2) AS total_2,
(total_1 + total_2) AS total_3
FROM data;</code>

This is because when PostgreSQL calculates total_3, total_1 and total_2 have not been defined yet.

The solution is to use a subquery (derived table):

<code class="language-sql">SELECT cost_1,
       quantity_1,
       cost_2,
       quantity_2,
       total_1 + total_2 AS total_3
FROM (
    SELECT cost_1, 
           quantity_1, 
           cost_2, 
           quantity_2, 
           (cost_1 * quantity_1) AS total_1,
           (cost_2 * quantity_2) AS total_2
    FROM data
) t;</code>

By creating the subquery t, we define total_1 and total_2 as available columns in the outer SELECT statement. There is no performance penalty with this approach. The outer query references the result set of the inner query so that total_3 can be calculated correctly.

The above is the detailed content of How Can I Use Calculated Columns from the Same Query in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!