From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Concatenation through SQL |
Date: | 2007-12-21 16:01:28 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F4218CFC1@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rick Innis | 2007-12-22 22:12:45 | Misnamed field in subquery does not cause error when field name exists in parent query |
Previous Message | Pavel Stehule | 2007-12-21 14:24:46 | Re: Concatenation through SQL |