Mysql Select Sum but last three records

One option uses window functions, available in MySQL 8.0:

select customer, sum(amount) total_amount
from (
    select a.*, row_number() over(partition by customer order by date desc) rn
    from tablea a
) a
where rn > 2
group by customer

In earlier versions, an alternative uses a correlated subquery that returns the third latest date per customer for filtering:

select customer, sum(amount) total_amount
from tablea a
where date <= (select a1.date from tablea a1 where a1.customer = a.customer order by a1.date desc limit 2, 1)
group by customer

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top