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, Sep 19 '19 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, Sep 19 '19 at 16:50
@Mike string_agg requires a group by. Why do you think it doesn't work with group by? — Jeremy, Sep 19 '19 at 16:54
@Gordon Linoff and if i have 20 columns, i should group by 20? — Bogdan, Sep 19 '19 at 17:07

score 0 · Answer 1 · answered Sep 19 '19 at 16:42

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

with group by don't work string_agg . For different one to many cases i have same rows but different lasts columns — Bogdan, Sep 19 '19 at 16:48

Can't join string array to string using string_agg

1 Answers1