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

@BeidreAhmed: presumably, the total for customer A should be 250 rather than 150. — GMB, Oct 29 '20 at 12:08
Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Oct 29 '20 at 12:19

GMB · Accepted Answer · 2020-10-29T12:05:53.333

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

edited Oct 29 '20 at 12:05

answered Oct 29 '20 at 11:48

GMB

216,147
25
84
135

this gives the error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by customer order by date desc) rn from table t' at line 3 @GMB – Beidre Ahmed Oct 29 '20 at 11:54
@BeidreAhmed: you are running a pre-8.0 version of MySQL. I edited my answer with an alternative. – GMB Oct 29 '20 at 12:06

Mysql Select Sum but last three records

1 Answers1