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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/s...tablefunc.html

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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 functionalities
>> from 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
>



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq