postgres alias sum-CodePudding

I'm trying to run a query that structured something like the following in Postgres, But I'm getting and issue with the column sum(). The query doesn't recognize the price alias. How do I achieve this?

select col1,
       i2f(....) as col2,
       (select ..... limit 1) as PRICE,
       sum(PRICE    1)
  from tbl1 a
 where a.".." = 1;

Error:

ERROR:  column "price" does not exist
LINE 4:            sum(PRICE    1)

CodePudding user response：

You can't use column alias in a subquery, group by or where clause condition.

you should use WITH (Common Table Expressions) or use the join command to join the table and use an alias.

with price as (
 select ..... limit 1
)
select col1,
       i2f(....) as col2,
       sum((select * from price)    1)
  from tbl1 a
 where a.".." = 1;

CodePudding user response：

Note: The question you asked is not the only important problem in your SQL.

You also have a general aggregation/GROUP BY problem as well, where some of your SELECT list expressions, in the presence of an aggregate function, are not functionally dependent on the GROUP BY terms. That could use a separate question and answer.

I'll address just your specific question here.

Basically, you're asking about a term in a <query expression> called a <query specification>, which has the following general form:

<query specification> ::=
     SELECT [ <set quantifier> ] <select list> <table expression>

Within this is your <select list>, which contains, among other things, <value expression>s. These <value expression>s may refer to columns of the corresponding <table expression>, which does not include any <derived column>s in the same <select list>.

(select ..... limit 1) as PRICE is a <derived column> in your <select list>, which means sum(PRICE 1) can't refer to this PRICE.

There are a few things you can do to allow access to PRICE. Mainly they involve:

Deriving PRICE within the corresponding <table expression>

That could be contained within a <derived table> or a <table reference> to a <with list element> within a <with clause>.

Derived table form:

SELECT ..., sum(PRICE    1)
  FROM (
     SELECT ..., expression AS PRICE
       FROM ...
     ) AS tname
...

WITH clause form:

WITH tname AS (
     SELECT ..., expression AS PRICE
       FROM ...
     )
SELECT ..., sum(PRICE    1)
  FROM tname
...