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

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

My Table is :

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.

I am looking for sum of the red circled numbers.

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

You provided tid, sum(capacity) in your SELECT statement, which means you will get the sum for each tid that exists.

Since each tid apparently exists only once in your dataset (at least in your image), I assume you get the same values returned from the table.

If you're interested in the sum, no matter which tid, then simply remove the tid column from the statement, like this:

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