Can't join string array to string using string_agg

Question

I have 5 tables, and i need select some columns from them. And some of tables have one to many relationship. And for this situations i use string_agg

I tried string_agg(cause, ', '), but after that i have error that i need group by all columns or be used in an aggregate function. Also i tried as there

SELECT p.id                    as person_id,
       p.name                  as name,
       p.surname               as surname,
       gr.registration_number  as registry_number,
       ac.order_date           as order_date,
       ac.note                 as note,
       string_agg(cccd.description, ', ')   as cause_description,
       string_agg(cccd.cause, ', ' order by cccd.id) as cause

from auditors.persons p
         left join auditors.governmental_register gr on p.id = gr.person_id
         left join auditors.auditor_certificates ac on p.id = ac.person_id
        left join auditors.certificate_change_cause_dictionary cccd on ac.cause_of_changes_id = cccd.id;

You are using an aggregation function without a group by. What are you trying to do? Sample data and desired results would really help. — Gordon Linoff
– Gordon Linoff, Commented Sep 19, 2019 at 16:40
@Gordon Linoff with group by don't work string_agg . For different one to many cases i have same rows but different lasts columns — Bogdan
– Bogdan, Commented Sep 19, 2019 at 16:50
@Mike string_agg requires a group by. Why do you think it doesn't work with group by? — Jeremy
– Jeremy, Commented Sep 19, 2019 at 16:54
@Gordon Linoff and if i have 20 columns, i should group by 20? — Bogdan
– Bogdan, Commented Sep 19, 2019 at 17:07

Gordon Linoff · Accepted Answer · 2019-09-19 16:42:01Z

0

You are obviously missing the group by:

SELECT p.id                    as person_id,
       p.name                  as name,
       p.surname               as surname,
       gr.registration_number  as registry_number,
       ac.order_date           as order_date,
       ac.note                 as note,
       string_agg(cccd.description, ', ')   as cause_description,
       string_agg(cccd.cause, ', ' order by cccd.id) as cause    
from auditors.persons p left join
     auditors.governmental_register gr
     on p.id = gr.person_id left join
     auditors.auditor_certificates ac
     on p.id = ac.person_id left join
     auditors.certificate_change_cause_dictionary cccd
     on ac.cause_of_changes_id = cccd.id
group by p.id, p.name, p.surname, gr.registration_number, ac.order_date, ac.note

answered Sep 19, 2019 at 16:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bogdan Over a year ago

with group by don't work string_agg . For different one to many cases i have same rows but different lasts columns

Collectives™ on Stack Overflow

Can't join string array to string using string_agg

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related