How do I generate a list of strings per id?

Question

I want to list what brands each customer buys. I have the same "customer_id" feilds showing up repeatedly with a name of different brands (shown as column title: "name") they purchased in this code. I'd like to group by the customer_id and show a list of brands for each customer_id. I get the error message:

"ERROR: function group_concat(character varying, unknown) does not exist ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

    CREATE TEMP TABLE customer_brandids AS 
SELECT receipts.receipt_id, receipts.customer_id, receipt_item_details1.brand_id
FROM receipts
LEFT JOIN receipt_item_details1
ON receipts.receipt_id = receipt_item_details1.receipt_id;

SELECT customer_brandids.customer_id, customer_brandids.brand_id, brands.name, GROUP_CONCAT(brands.name,',')
FROM customer_brandids
INNER JOIN brands
ON customer_brandids.brand_id = brands.brand_id
GROUP by customer_id

Where in the [Postgres manual](https://www.postgresql.org/docs/current/static/functions.html) did you find `group_concat`? — , Oct 04 '18 at 20:16

score 1 · Accepted Answer · answered Oct 04 '18 at 20:02

1

CREATE TEMP TABLE customer_brandids AS 
SELECT receipts.receipt_id, receipts.customer_id, receipt_item_details1.brand_id
FROM receipts
LEFT JOIN receipt_item_details1
ON receipts.receipt_id = receipt_item_details1.receipt_id;

SELECT customer_brandids.customer_id, customer_brandids.brand_id, brands.name, string_agg(brands.name,',')
FROM customer_brandids
INNER JOIN brands
ON customer_brandids.brand_id = brands.brand_id
GROUP by customer_id

answered Oct 04 '18 at 20:02

Cetin Basoz

22,495
3
31
39

Thanks when I ran it and replaced "GROUP BY customer_id with "GROUP BY customer_brandids.customer_id" I got this error mesage: "column "customer_brandids.brand_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT customer_brandids.customer_id, customer_brandids.bran... How do I just aggregate by customer_id? – lschra01 Oct 04 '18 at 20:09
@lschra01, you can't unless you make it to an aggregate. I was ignorant to see you had non-aggregate fields there and not included in group by. if you don't care which brand_id you get per customer_id or it is unique per customer_id (same for brands.name) then you could do something like: max(customer_brandids.brand_id) as brand_id. - or simply add them to group by. – Cetin Basoz Oct 04 '18 at 23:08

S-Man · Answer 2 · 2018-10-04T20:31:57.587

1

This aggregates only the brand names:

SELECT cb.customer_id, ARRAY_AGG(b.name) as brand_names
FROM customer_brandids cb
INNER JOIN brands b
ON cb.brand_id = b.brand_id
GROUP by cb.customer_id

If you want a list of brand IDs as well:

SELECT 
    cb.customer_id, 
    ARRAY_AGG(b.brand_id) as brand_ids,
    ARRAY_AGG(b.name) as brand_names
FROM customer_brandids cb
INNER JOIN brands b
ON cb.brand_id = b.brand_id
GROUP by cb.customer_id

If you need the list as string list use string_agg instead of array_agg

SELECT 
    cb.customer_id, 
    string_agg(b.brand_id, ',') as brand_ids, -- delete this line if you only need the names
    string_agg(b.name, ',') as brand_names
FROM customer_brandids cb
INNER JOIN brands b
ON cb.brand_id = b.brand_id
GROUP by cb.customer_id

edited Oct 04 '18 at 20:31

answered Oct 04 '18 at 20:13

S-Man

22,521
7
40
63

Thanks! just need to list the brands each customer _id buys with comma's seperating each brand. – lschra01 Oct 04 '18 at 20:15
1

I added a string_agg query. Better? – S-Man Oct 04 '18 at 20:17
I didn’t? I thought I did – lschra01 Oct 05 '18 at 22:34

How do I generate a list of strings per id?

2 Answers2