0

I have table like this :

booking_id, state   
01          red      
01          green   
01          black   
02          red   
02          green   
03          red

I wanted to get something like this :

01          red,green,black       
02          red,green      
03          red

So I added string_agg function to my query, but the field ALLSTATES is returning a result like this :

01         red,red,red         
01         green,green,green           
01         black,black,black       
02         red,red        
02         green,green         
03         red

This is my query :

SELECT bookings.id,
bookings.confirmed_at, 
bookings.disputed, 
bookings.no_show_claimed,
bookings.disputed_at,
bookings.no_show_blocked_until,
bookings.user_id,bookings.refunded,
bookings.refunded_at,invoices.id as invoice_id,
invoices.state,
invoices.currency_to_eur, 
max(lessons.time), 
greatest(max(lessons.time),
bookings.no_show_blocked_until,
bookings.confirmed_at), 
string_agg(',',invoices.state) as ALLSTATES


FROM bookings LEFT JOIN invoice_lines ON invoice_lines.booking_id = bookings.id 
LEFT JOIN invoices ON invoices.id = invoice_lines.invoice_id
 LEFT JOIN lesson_bookings ON lesson_bookings.booking_id = bookings.id 
LEFT JOIN lessons ON lessons.id = lesson_bookings.lesson_id 

GROUP BY (bookings.id,invoices.id)

any idea how to correct this please ?

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Firas Von Zgr
  • 3
  • 2
  • 4
  • You're using the input for string_agg() in the wrong order, you set the delimiter first and the value second, while it should be: string_agg ( value text, delimiter text ) https://www.postgresql.org/docs/current/functions-aggregate.html – Frank Heikens Jan 21 '21 at 08:57

1 Answers1

1

You seem to want:

string_agg(distinct invoices.state, ',') as ALLSTATES

You may also need to adjust the group by. However, your query is way more complicated than the sample data you have provided, so it is unclear exactly what changes are needed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your answer Gordon. you're right, the problem is coming from the group by I'm using. is there a way to keep it and add another group by that works with the string_agg I added ? – Firas Von Zgr Jan 21 '21 at 09:13
  • @FirasVonZgr . . . I would suggest asking a *new* question. This question is really hard to follow because the explanation and query are just not related to each other. – Gordon Linoff Jan 21 '21 at 13:54