CODEWITHSUNDEEP
sqlpostgresql
Ladu anand
Ladu anand

Reputation: 650

Retrieving the last date from payments table postgresql

I have a table which contains the payment details .

    id  payment_date    payment_schedule    amount  status  app_id
1   10/12/2020  9/12/2020   2000    TRUE    30653
2   null    9/12/2020   1000    FALSE   30653
3   null    9/22/2020   1000    FALSE   34046
4   null    9/13/2020   1000    FALSE   34046
5   9/12/2020   9/12/2020   1000    TRUE    34046
6   null    9/22/2020   1000    FALSE   35046
7   10/15/2020  10/13/2020  500 TRUE    35046
8   10/12/2020  10/12/2020  1000    TRUE    35046

My requirement is to get the below output :

last_payment_date is the last payment received i.e when status is true , payment is received and payment_date is filled . sum is the sum of payments received . 

app_id  last_payment_date   sum
30653   9/12/2020   2000
34046   10/12/2020  1000
35046   10/15/2020  1500

I am working on the below query and able to calculate the sum but not able to find the last date when the payment was made .

select a.id,
    sum(case when aps.status = true then aps.amount  end) as suum
    from applications a inner join application_payment_schedule aps
    on a.id = aps.app_id 
    group by a.id;

Upvotes: 0

Views: 169

Answers (2)

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

Include payment_date in your select list and filter it by max. 

select a.id,sum(aps.amount) as suum,max(a.payment_date) as payment_date
    from applications a inner join application_payment_schedule aps
    on a.id = aps.app_id where aps.status = true
    group by a.id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This sounds like aggregation:

select a.id,
       max(aps.payment_date) filter (where aps.status) as last_payment
       sum(aps.amount) filter (where aps.status) as suum
from applications a inner join
     application_payment_schedule aps
     on a.id = aps.app_id 
group by a.id;

It is unclear if you want the filter on both the last payment and the sum. Your question suggests only on the last payment, but your question applies to the sum as well.

The above can be simplified by moving the filter to a where clause:

select a.id,
       max(aps.payment_date) as last_payment
       sum(aps.amount) filter as suum
from applications a inner join
     application_payment_schedule aps
     on a.id = aps.app_id 
where aps.status
group by a.id;

Upvotes: 2

Related Questions