How to order strings in "string_agg" for window function (postgresql)?

Question

I have two tables: "debt_period" and "payments". I need to take a view of them. There is a situation when can be a few payments for one period. So in such case, I have to sum the payment values in one column and list the dates separated by a comma in the other column. I use string_agg(to_char(p.payment_date, 'DD.MM.YYYY') and window function. This works fine. The problem is that dates in the string are not in chronological order. How can I order them?

CREATE MATERIALIZED VIEW base.v_period_payments AS
SELECT DISTINCT
dp.card_id,
dp.year, 
dp.month, 
dp.debt_amount, 
string_agg(to_char(p.payment_date, 'DD.MM.YYYY'), ', ') OVER ( PARTITION BY p.period_id) AS 
pay_dates 
FROM base.debt_period dp
LEFT JOIN base.payments p on (p.period_id = dp.id)
WHERE dp.card_id = '№001137'   
ORDER BY dp.card_id

@a_horse_with_no_name There is an error: [0A000] ERROR: aggregate ORDER BY is not implemented for window functions — nes-p
– nes-p, Commented Jul 31, 2017 at 8:22

Mesbah Gueffaf · Accepted Answer · 2017-07-31 07:43:27Z

1

try to use used this :

change

LEFT JOIN base.payments p on (p.period_id = dp.id)

by

LEFT JOIN  (select * from base.payments  order by period_id,payment_date) p on (p.period_id = dp.id)

final result

CREATE MATERIALIZED VIEW base.v_period_payments AS
SELECT DISTINCT
dp.card_id,
dp.year, 
dp.month, 
dp.debt_amount, 
string_agg(to_char(p.payment_date, 'DD.MM.YYYY'), ', ') OVER ( PARTITION BY p.period_id) AS 
pay_dates 
FROM base.debt_period dp
LEFT JOIN  (select * from base.payments  order by period_id,payment_date) p on (p.period_id = dp.id)
WHERE dp.card_id = '№001137'   
ORDER BY dp.card_id

answered Jul 31, 2017 at 7:43

Mesbah Gueffaf

5581 gold badge7 silver badges23 bronze badges

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

2 Comments

nes-p Over a year ago

One more question. Without condition 'WHERE dp.card_id = '№001137' ' the query doesn't work properly. Could you explain why and what to do with it?

Mesbah Gueffaf Over a year ago

You can send me the script for both tables So I can help you more Because I think they have no problem if you remove the part where

João Ramiro · Accepted Answer · 2020-10-21 12:08:19Z

I was looking for this functionlity and there is an alternative to what @Mesbah answered

See here: https://www.postgresql.org/docs/9.0/sql-expressions.html 4.2.8. Window Function Calls

Basically you can define what o ORDER BY, OVER BY as well as specy what rows to include in the window (in the example below the window is over all rows)

CREATE MATERIALIZED VIEW base.v_period_payments AS
SELECT DISTINCT
dp.card_id,
dp.year, 
dp.month, 
dp.debt_amount, 
string_agg(to_char(p.payment_date, 'DD.MM.YYYY'), ', ') OVER ( PARTITION BY p.period_id
ORDER BY payment_date
range between unbounded preceding  and unbounded following  -- used to aggregate full range
) AS 
pay_dates 
FROM base.debt_period dp
LEFT JOIN base.payments p on (p.period_id = dp.id)
WHERE dp.card_id = '№001137'   
ORDER BY dp.card_id

Collectives™ on Stack Overflow

How to order strings in "string_agg" for window function (postgresql)?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related