Re: How to reference a subquery column alias?

Hello

Yesterday a list user solved me a problem with a sententence with two
subqueries. The solution was this:

SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos
WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal,
(SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND
hora=max(ooo.hora)) as max_caudal from historicos AS ooo GROUP BY
remota_id ORDER BY remota_id;

The issue now is i want to do some calculations with the subqueries
columns (min_caudal and max_caudal), for example adding them. (Get a new
column with max_caudal and min_caudal (alias) added)

I have tried to add in the SELECT;
,max_caudal+min_caudal as diferencia
,ooo.max_caudal+ooo.min_caudal as diferencia
,historicos.max_caudal+historicos.min_caudal as diferencia
,(SELECT max_caudal+min_caudal) as diferencia

I've read the SELECT and Table Expressions documentation pages, but
didn't found a solution.

Can anyone tell me how to reference or make the calculation with those
alias names?

TIA

Best,

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/11523 - Mon Aug 9 19:20:40 2010
by Markus Madlener @ http://www.copfilter.org

Hi,

Just wrap your expression with another SELECT and operate with the aliases like

SELECT *, min_caudal + max_caudal AS diferencia FROM (
...your expression...
)

2010/8/9 José María Terry Jiménez <jtj(at)tssystems(dot)net>:
> Hello
>
> Yesterday a list user solved me a problem with a sententence with two
> subqueries. The solution was this:
>
> SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE
> remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT
> caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora))
> as max_caudal from historicos AS ooo GROUP BY remota_id ORDER BY remota_id;
>
> The issue now is i want to do some calculations with the subqueries columns
> (min_caudal and max_caudal), for example adding them. (Get a new column with
> max_caudal and min_caudal (alias) added)
>
> I have tried to add in the SELECT;
> ,max_caudal+min_caudal as diferencia
> ,ooo.max_caudal+ooo.min_caudal as diferencia
> ,historicos.max_caudal+historicos.min_caudal as diferencia
> ,(SELECT max_caudal+min_caudal) as diferencia
>
> I've read the SELECT and Table Expressions documentation pages, but didn't
> found a solution.
>
> Can anyone tell me how to reference or make the calculation with those alias
> names?
>
> TIA
>
> Best,
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
> AntiVirus: ClamAV 0.95.2/11523 - Mon Aug  9 19:20:40 2010
> by Markus Madlener @ http://www.copfilter.org
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802

Hello Sergey

Thanks by your answer, this worked after i add at the end an AS xxx
clause, because an error telling me something about subqueries in FROM
must have an alias, so i did it:

SELECT *, min_caudal + max_caudal AS diferencia FROM (
...your expression...
) AS temp

and worked

Best,

Sergey Konoplev escribió:
> Hi,
>
> Just wrap your expression with another SELECT and operate with the aliases like
>
> SELECT *, min_caudal + max_caudal AS diferencia FROM (
> ...your expression...
> )
>
> 2010/8/9 José María Terry Jiménez <jtj(at)tssystems(dot)net>:
>
>> Hello
>>
>> Yesterday a list user solved me a problem with a sententence with two
>> subqueries. The solution was this:
>>
>> SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE
>> remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT
>> caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora))
>> as max_caudal from historicos AS ooo GROUP BY remota_id ORDER BY remota_id;
>>
>> The issue now is i want to do some calculations with the subqueries columns
>> (min_caudal and max_caudal), for example adding them. (Get a new column with
>> max_caudal and min_caudal (alias) added)
>>
>> I have tried to add in the SELECT;
>> ,max_caudal+min_caudal as diferencia
>> ,ooo.max_caudal+ooo.min_caudal as diferencia
>> ,historicos.max_caudal+historicos.min_caudal as diferencia
>> ,(SELECT max_caudal+min_caudal) as diferencia
>>
>> I've read the SELECT and Table Expressions documentation pages, but didn't
>> found a solution.
>>
>> Can anyone tell me how to reference or make the calculation with those alias
>> names?
>>
>> TIA
>>
>> Best,
>>
>>

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/11523 - Mon Aug 9 19:20:40 2010
by Markus Madlener @ http://www.copfilter.org