Trying to get sum of specific column cells for last 4 rows sum in MySQL

I am trying to get sum of capacity column of last 4 rows of a table in MySQL.

My Table is :

enter image description here

My SQL is :

SELECT
tid,
sum(capacity)
FROM captable 
ORDER BY tid DESC LIMIT 4

Result is giving different sum. It should be 150+200+250+300 = 900.

enter image description here

I am looking for sum of the red circled numbers.

You could order using subquery and apply SUM in the outer query. This query take in consideration that tid is auto_inrement:

SELECT sum(t1.capacity)
FROM ( SELECT tid,capacity
       FROM captable
       ORDER BY  tid DESC LIMIT 4
      ) as t1 ;

Result:

last_4_rows_sum
900

Demo:

You can use a sub query,


SELECT tid,
sum(capacity)
from
(SELECT
tid,
capacity
FROM captable 
ORDER BY tid DESC LIMIT 4) tbl;

You can also use row_number() if required,

SELECT tid,
sum(capacity)
from
(SELECT
tid,
capacity,
row_number() over(order by capacity desc) as rnk
FROM captable) tbl where rnk between 1 and 4;

Related