How can I get the target table in PostgreSQL?

Question

The table look like this

number      day amount
013xxxxxxxx 1   62773
013xxxxxxxx 8   52963
013xxxxxxxx 9   10810
013xxxxxxxx 10  84193
013xxxxxxxx 11  91791
019xxxxxxxx 1   89055
019xxxxxxxx 3   85366
019xxxxxxxx 5   47318

In the above table. It is source table. Here we can see all the day number is not available. So I need to add those unavailable rows with amount 0.

I want the table like this.

number      day amount
013xxxxxxxx 1   62773
013xxxxxxxx 2   0
013xxxxxxxx 3   0
013xxxxxxxx 4   0
013xxxxxxxx 5   0
013xxxxxxxx 6   0
013xxxxxxxx 7   0
013xxxxxxxx 8   52963
013xxxxxxxx 9   10810
013xxxxxxxx 10  84193
013xxxxxxxx 11  91791
019xxxxxxxx 1   89055
019xxxxxxxx 2   0
019xxxxxxxx 3   85366
019xxxxxxxx 4   0
019xxxxxxxx 5   47318

Why did you use the Oracle tag? Do you use PostgreSQL (as the title suggests), or both, or what? — Littlefoot
– Littlefoot, Commented Jan 17, 2022 at 9:10
my mistake,.... I have edited the post... I need for postgres only. — Backstreet Imrul
– Backstreet Imrul, Commented Jan 17, 2022 at 9:12

Philippe · Accepted Answer · 2022-01-17 09:45:37Z

2

You can write this query (Result here)

with x as (SELECT distinct test.number,generated_day FROM generate_series(1, 31) as generated_day, test),
y as (SELECT distinct test.number, MAX(day) OVER (PARTITION BY number) AS max_day FROM test)
select x.number,x.generated_day,coalesce(t.amount,0)
from x left join test t on t."number" = x.number and t.day = x.generated_day
where x.generated_day <= (SELECT MAX(day) max_day FROM test where test.number = x.number) 
order by 1, 2

answered Jan 17, 2022 at 9:45

Philippe

1,8376 silver badges19 bronze badges

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

1 Comment

Backstreet Imrul Over a year ago

Thanks a lot... It works like a champ....!!!

Tim Biegeleisen · Accepted Answer · 2022-01-17 09:52:25Z

1

Using a calendar table approach:

WITH cte AS (
    SELECT number, MIN(day) AS min_day, MAX(day) AS max_day
    FROM yourTable
    GROUP BY number
)

SELECT n.number, s.day, COALESCE(t2.amount, 0) AS amount
FROM (SELECT DISTINCT number FROM yourTable) n
CROSS JOIN (SELECT * FROM generate_series(1, 31)) AS s(day)
INNER JOIN cte 
    ON t.number = n.number
LEFT JOIN yourTable t2
    ON t2.number = n.number AND t2.day = s.day
WHERE s.day BETWEEN t.min_day AND t.max_day
ORDER BY n.number, s.day;

Demo

edited Jan 17, 2022 at 9:52

answered Jan 17, 2022 at 9:16

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Backstreet Imrul Over a year ago

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "s"¶ Position: 324

Tim Biegeleisen Over a year ago

@BackstreetImrul I fixed it, tough question, check the demo.

Collectives™ on Stack Overflow

How can I get the target table in PostgreSQL?

The table look like this

I want the table like this.

2 Answers 2

1 Comment

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The table look like this

I want the table like this.

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related