From: Philippe Lang <philippe.lang@attiksystem.ch>
To: pgsql-sql@postgresql.org
Subject: Re: Concatenation through SQL
Date: Fri, 21 Dec 2007 17:01:28 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CFC1@poweredge.attiksystem.ch> (raw)
References: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch>
<20071221105813.GA18247@KanotixBox>
<6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch>
<162867790712210624y1e3454ffw8d4bffff69819335@mail.gmail.com>
Pavel Stehule wrote:
>> SELECT
>> appointments.name AS appointment,
>> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
>> appointments.id) AS num_employees,
>> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
>> WHERE e.appointment_id = appointments.id) AS employees FROM
>> appointments ---------------------------------------
>>
>
> hello,
>
> one note: you can merge your two subselect into one. It's unimportant
> on 100 rows, but it's can be important on thousands rows.
>
> nice a day
> Pavel
Hi Pavel,
Since subselects must return one single column, is that what you are
talking about? I have just modified the code you have posted before.
---------------------------
SELECT
name AS appointment,
num_employees,
employees
FROM
(
SELECT
appointment_id,
COUNT(*) AS num_employees,
(SELECT array_to_string(array_accum(name),', ') FROM employees WHERE
e.appointment_id = appointment_id) AS employees
FROM employees e
GROUP BY appointment_id
)s
JOIN appointments a on a.id = s.appointment_id;
---------------------------
Philippe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch
Subject: Re: Concatenation through SQL
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F4218CFC1@poweredge.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox