Mysql Select Sum but last three records

Question

I have table with fields Customer date and amount I want to sum Amount grouped by customer except the last two amounts of every customer by date sample data

 customer     date               amount
  a           2020-10-1             100
  a           2020-10-2             150
  a           2020-10-3             30
  a           2020-10-4             20
  b           2020-10-1             1
  b           2020-10-5             13
  b           2020-10-7             50
  b           2020-10-9             18

desired result

  Customer    Amount
   A          150
   B           14

something like

select Customer , 
SUM(amount- last 2 amount)
From TableA
Group By Customer

Answer 1

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