Referencing a calculated field in SQL statement

Question

I have the following schema

CREATE  TABLE QUOTE (id int, amount int);
CREATE  TABLE QUOTE_LINE (id int, quote_id int, line_amount int);


INSERT INTO QUOTE VALUES(1, 100);
INSERT INTO QUOTE VALUES(2, 200);
INSERT INTO QUOTE VALUES(3, 100);
INSERT INTO QUOTE VALUES(4, 300);



INSERT INTO QUOTE_LINE VALUES(1, 1, 5);
INSERT INTO QUOTE_LINE VALUES(2, 1, 6);
INSERT INTO QUOTE_LINE VALUES(3, 1, 4);
INSERT INTO QUOTE_LINE VALUES(4, 1, 2);
INSERT INTO QUOTE_LINE VALUES(1, 2, 5);
INSERT INTO QUOTE_LINE VALUES(2, 2, 5);
INSERT INTO QUOTE_LINE VALUES(3, 2, 5);
INSERT INTO QUOTE_LINE VALUES(4, 2, 5);

And I need to run the following query:

SELECT QUOTE.id, 
line_amount*12 AS amount,
amount*2 as amount_doubled
from QUOTE_LINE
LEFT JOIN QUOTE ON QUOTE_LINE.quote_id=QUOTE.id;

The 3rd line in the query amount*2 as amount_double needs to reference the amount calculated in the prior line i.e. line_amount*12 AS amount.

However if I run this query, it picks the amount from the QUOTE table instead the amount that was calculated. How can I make my query use the calculated amount without changing the name of the calculated field?

Here is the sqlfiddle for this: http://sqlfiddle.com/#!17/914b2/1

Note: I understand that I can create a sub-query, CTE or a lateral join, but the tables I am working are very very wide tables, and the queries have many many joins. As such, I need to keep the LEFT INNER JOINS and also I don't always know if a calculated field will be duplicated in JOINed table or not. Table structures change.

Unrelated to your problem, but: Postgres 9.3 and 9.4 are [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. — , Feb 18 '21 at 15:14

score 1 · Answer 1 · answered Feb 18 '21 at 01:13

Move the definition to the FROM clause using a LATERAL JOIN:

select q.id, v.amount, v.amount * 2 as as amount_doubled
from QUOTE_LINE ql left join
     QUOTE q
     on ql.quote_id = q.id CROSS JOIN LATERAL
     (values (line_amount*12)) v(amount);

You can also use a subquery or CTE, but I like the lateral join method.

Note: I would expect QUOTE to be the first table in the LEFT JOIN.

score 0 · Answer 2 · answered Feb 18 '21 at 03:35

Qualify all column names with the table name and use a subquery:

SELECT q.id,
       q.amount,
       q.amount * 2 AS amount_doubled
FROM (SELECT quote.id,
             quote_line.line_amount * 12 AS amount,
      FROM quote_line
         LEFT JOIN quite
            ON quote_line.quote_id = quote.id
     ) AS q;

score -1 · Answer 3 · answered Feb 18 '21 at 23:59

Just a little simple algebra resolves the issue quite easily. It is clear that calculated amount is 12 times the line_amount and that amount_doubles is 2 times that. So

select q.id
     , ql.line_amount*12   as amount 
     , ql.line_amount*12*2 as amount_doubled  
  from quote_line ql 
  left join quote q
     on ql.quote_id = q.id;

However, child left join parent seems strange as it basically says "Give me the quote line amounts where there is no quote". One would hope a FK from line to quote would prevent that from happening. If so then a inner join would suffice. Further if the id is the only column from quote the join can removed by taking quote_id from quote_line. So perhaps reducing to:

select ql.quote_id        as id
     , ql.line_amount*12  as amount 
     , ql.line_amount*24  as amount_doubled  
  from quote_line ql;

Referencing a calculated field in SQL statement

3 Answers3