Hello postgres=# select name, count, employes from (select appointment_id, count(*), (SELECT array_to_string(ARRAY(SELECT name from employees where appointment_id = e.appointment_id),',')) as employes from employees e group by appointment_id)s join appointments a on a.id = s.appointment_id; name | count | employes ------+-------+---------------- app2 | 2 | emp1,emp4 app1 | 3 | emp1,emp2,emp3 (2 rows)
regards Pavel Stehule for longer table is better define own aggregate function. On 21/12/2007, Philippe Lang <[EMAIL PROTECTED]> wrote: > Hi, > > Here is small reduced test database: > > ------------------------------ > CREATE TABLE appointments > ( > id integer, > name varchar(32), > CONSTRAINT appointments_pkey PRIMARY KEY (id) > ); > > CREATE TABLE employees > ( > id integer, > appointment_id integer, > name varchar(32), > CONSTRAINT employees_pkey PRIMARY KEY (id), > CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id) > REFERENCES appointments (id) > ); > > INSERT INTO appointments VALUES (1, 'app1'); > INSERT INTO employees VALUES (1, 1, 'emp1'); > INSERT INTO employees VALUES (2, 1, 'emp2'); > INSERT INTO employees VALUES (3, 1, 'emp3'); > > INSERT INTO appointments VALUES (2, 'app2'); > INSERT INTO employees VALUES (4, 2, 'emp1'); > INSERT INTO employees VALUES (5, 2, 'emp4'); > ------------------------------ > > > I'm trying to write an SQL query that would return this: > > --------------------------------------------------- > appointment count_employees employees > --------------------------------------------------- > app1 3 emp1, emp2, emp3 > app2 2 emp1, emp4, > --------------------------------------------------- > > First part is easy to write: > > ------------------------------ > SELECT > appointments.name AS appointment, > (SELECT COUNT(*) FROM employees AS e where e.appointment_id = > appointments.id) AS num_employees > FROM appointments > ------------------------------ > > ... But concatenating employees name is harder, at least for me... > > I'm convinced this can be done with Set Returning Functions and a bit of > plpgsql, but I was wondering if it would possible to write some kind of > extension to Postgresql (operator?) that would allow this kind of > syntax: > > ------------------------------ > SELECT > appointments.name AS appointment, > (SELECT COUNT(*) FROM employees AS e where e.appointment_id = > appointments.id) AS num_employees > (SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id = > appointments.id) AS employees > FROM appointments > ------------------------------ > > ... where CONCAT suggest we want to concatenate the variable inside, > with the separator ', ' inbetween. > > Thanks for your tips! > > > Philippe Lang > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
