Обсуждение: 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@tssystems.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@postgresql.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.ru@gmail.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@tssystems.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