Re: Concatenation through SQL - Mailing list pgsql-sql

From Philippe Lang
Subject Re: Concatenation through SQL
Date
Msg-id 6C0CF58A187DA5479245E0830AF84F4218CFC1@poweredge.attiksystem.ch
Whole thread Raw
In response to Concatenation through SQL  ("Philippe Lang" <philippe.lang@attiksystem.ch>)
List 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
employeesWHERE 
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


pgsql-sql by date:

Previous
From: "Pavel Stehule"
Date:
Subject: Re: Concatenation through SQL
Next
From: Rick Innis
Date:
Subject: Misnamed field in subquery does not cause error when field name exists in parent query