Thread: using column alias to make operations
Hello, Given this SQL: SELECT c.name AS Customer, sum(rc.days_total) AS Days, count(distinct(rc.id)) AS Cages FROM customers c INNER JOIN reserved_cages rc ON c.ID=rc.customer_id GROUP BY c.name Now I want to display the Days/Cages, and I want to use the column aliases for it, like this: SELECT c.name AS Customer, sum(rc.days_total) AS Days, count(distinct(rc.id)) AS Cages, (Days/Cages) FROM customers c INNER JOIN reserved_cages rc ON c.ID=rc.customer_id GROUP BY c.name But this doesn't work, but this works: SELECT c.name AS Customer, sum(rc.days_total) AS Days, count(distinct(rc.id)) AS Cages, (sum(rc.days_total)/ count(distinct(rc.id))) AS AVG FROM customers c INNER JOIN reserved_cages rc ON c.ID=rc.customer_id GROUP BY c.name But I think this is double-time as It has to make twice the same operations. I thought I could use the column alias, because in the ORDER BY clausule I can use them ... thanks ! raimon
am Wed, dem 23.01.2008, um 9:10:09 +0100 mailte Raimon Fernandez folgendes: > I thought I could use the column alias, because in the ORDER BY > clausule I can use them ... You can't, because first PG fetch the result, this result has the column-aliases. A consecutively ORDER BY can access this aliases, but not the SELECT self. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Wed, dem 23.01.2008, um 10:53:51 +0100 mailte Raimon Fernandez folgendes: > > On 23/01/2008, at 10:36, A. Kretschmer wrote: > > >am Wed, dem 23.01.2008, um 9:10:09 +0100 mailte Raimon Fernandez > >folgendes: > >>I thought I could use the column alias, because in the ORDER BY > >>clausule I can use them ... > > > >You can't, because first PG fetch the result, this result has the > >column-aliases. A consecutively ORDER BY can access this aliases, > >but not > >the SELECT self. > > ok, thanks > > and PG knows that there are two identical operations, and just do it > once and use that value, or does it twice ? Yes, of course, the former. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello, I can't find any reference in PostgreSQL documentation, if this is possible: I want to have some rows in one column, with the values separated for example by a comma. SELECT name FROM animals WHERE customer_id = '5' - cat - dog - turtle I would like to have this like this: - cat, dog, turtle So I could issue a SELECT like this: SELECT customer.name, (SELECT name FROM animals WHERE customer_id = customer.id) AS animals FROM customer name animals ------- ----------- Peter dog,cat Lisa turtle Anthony bird,elephant I could find info about PIVOT and some other extra functionalities from other databases like Oracle, SQL server, ... thanks, raimon
On Feb 2, 2008 10:13 AM, Raimon <coder@montx.com> wrote: > Hello, > > I can't find any reference in PostgreSQL documentation, if this is > possible: > > I want to have some rows in one column, with the values separated for > example by a comma. > > SELECT name FROM animals WHERE customer_id = '5' > > - cat > - dog > - turtle > > I would like to have this like this: > > - cat, dog, turtle > > > So I could issue a SELECT like this: > > SELECT customer.name, (SELECT name FROM animals WHERE customer_id = > customer.id) AS animals FROM customer > > name animals > ------- ----------- > Peter dog,cat > Lisa turtle > Anthony bird,elephant > > I could find info about PIVOT and some other extra functionalities > from other databases like Oracle, SQL server, ... > I think, here: http://www.postgresql.org/docs/8.3/static/tablefunc.html
am Sat, dem 02.02.2008, um 16:13:20 +0100 mailte Raimon folgendes: > Hello, > > I can't find any reference in PostgreSQL documentation, if this is > possible: > > I want to have some rows in one column, with the values separated for > example by a comma. > > SELECT name FROM animals WHERE customer_id = '5' > > - cat > - dog > - turtle > > I would like to have this like this: > > - cat, dog, turtle test=*# select * from animals ; id | name ----+-------- 5 | cat 5 | dog 5 | turtle (3 rows) test=*# select array_to_string(array(select name from animals where id=5), ', '); array_to_string ------------------ cat, dog, turtle HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
thanks, it's interesting ...
rai
On 02/02/2008, at 16:54, Mike Ellsworth wrote:
I could find info about PIVOT and some other extra functionalitiesfrom other databases like Oracle, SQL server, ...
I think, here: http://www.postgresql.org/docs/8.3/static/tablefunc.html
thanks ! r. On 02/02/2008, at 17:04, A. Kretschmer wrote: > am Sat, dem 02.02.2008, um 16:13:20 +0100 mailte Raimon folgendes: >> Hello, >> >> I can't find any reference in PostgreSQL documentation, if this is >> possible: >> >> I want to have some rows in one column, with the values separated for >> example by a comma. >> >> SELECT name FROM animals WHERE customer_id = '5' >> >> - cat >> - dog >> - turtle >> >> I would like to have this like this: >> >> - cat, dog, turtle > > test=*# select * from animals ; > id | name > ----+-------- > 5 | cat > 5 | dog > 5 | turtle > (3 rows) > > test=*# select array_to_string(array(select name from animals where > id=5), ', '); > array_to_string > ------------------ > cat, dog, turtle > > > HTH, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >