Postgres: string aggregation and concatenation

Question

If I have a query like this:

SELECT 
    u.client_code, 
    max(d.created_at) as last_document_created_at, 
    u.brand_id, 
    t.name as template_name, 
    count(d)
FROM users u
    INNER JOIN documents d ON u.id = d.user_id
    INNER JOIN templates t ON t.id = d.template_id
GROUP BY 1, 3, 4

Which returns information like this:

client_code last_document_created_at    brand_id template_name  count
---------------------------------------------------------------------
client1     2017-12-06 10:03:47 +1100   39       newsletter_r   1
client1     2017-12-05 15:23:24 +1100   39       Other media    5
client2     2017-12-21 17:07:11 +1100   39       newsletter_r   4
client3     2018-01-11 12:10:43 +1100   39       newsletter_r   2
client3     2017-12-06 11:45:21 +1100   39       Other media    1

What are my options to concatenate the template_name and count fields so that each user (represented in u.client_code) is on one line? I know I can call string_agg the column like so:

...
string_agg(distinct t.name, ', ') as template_name, 
...

But that of course ruins the respective counts:

newsletter_r, Other media   6

Update

I could do this:

string_agg(concat_ws(': ', t.name::text, count(d)::text), ', ') as template_count

But that gives me an error:

aggregate function calls cannot be nested LINE 5: string_agg(concat_ws(': ', t.name::text, count(d)::text)... ^ : SELECT u.client_code,

ravioli · Accepted Answer · 2018-02-06 03:46:06Z

1

Not sure how you want to format your concatenated field, but have you tried putting your original query into a sub-query and applying a string_agg to it? Something like this:

SELECT client_code, STRING_AGG(template_name || template_count, ',') 
FROM (
    SELECT 
        u.client_code, 
        MAX(d.created_at) AS last_document_created_at, 
        u.brand_id, 
        t.name AS template_name, 
        COUNT(d) AS template_count
    FROM users u
    INNER JOIN documents d ON u.id = d.user_id
    INNER JOIN templates t ON t.id = d.template_id
    GROUP BY 1, 3, 4
) src
GROUP BY client_code

I haven't tested it, so you may have some syntax errors. Let me know if that works.

answered Feb 6, 2018 at 3:46

ravioli

3,8213 gold badges17 silver badges35 bronze badges

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

6 Comments

t56k Over a year ago

That works pretty well! I don't know how to get the last_document_created_at column through, though, because I'm forced to group_by that column which splits u.client_code into many rows: column "src.last_document_created_at" must appear in the GROUP BY clause or be used in an aggregate function

ravioli Over a year ago

Well, how do you determine which last_document_created_at value to return for each client_code? Or do you just include it in the string_agg?

t56k Over a year ago

I just did another agg: (array_agg(last_document_created_at))[1] as last_created. Done (I think?). Thanks so much!

ravioli Over a year ago

Woohoo! Glad it worked :) Another option is to apply a MAX -- MAX(last_document_created_at).

t56k Over a year ago

Oh durr, ha. Thanks again!

|

Gordon Linoff · Accepted Answer · 2018-02-06 04:10:36Z

0

I think you want something like this:

SELECT u.client_code, 
       max(d.created_at) as last_document_created_at, 
       u.brand_id, 
       string_agg(t.name, ',') as template_name, 
       count(distinct d.id)
FROM users u INNER JOIN
     documents d
     ON u.id = d.user_id INNER JOIN
     templates t
     ON t.id = d.template_id
GROUP BY 1, 3;

edited Feb 6, 2018 at 4:10

answered Feb 6, 2018 at 3:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

t56k Over a year ago

There's an error in your string_agg() (it takes two arguments). Also this doesn't produce anything different than my query when that's fixed.

Collectives™ on Stack Overflow

Postgres: string aggregation and concatenation

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related