CODEWITHSUNDEEP
sqlpostgresqlstring-agg
Firas Von Zgr
Firas Von Zgr

Reputation: 3

string_agg not behaving correctly

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 ?

Upvotes: 0

Views: 567

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

Related Questions