Subtract values on column "cantidad"

Question

I need to subtract (cantidad 1 - cantidad 0) (cantidad 2 - 1 cantidad) (cantidad 3 - cantidad 2) for each line (that is my key field).

My SQL:

SELECT linea, producto, cantidad, operario, fecha 
FROM registro_cantidad_producida

Current Result:

Expected Result:

What do you mean by "cantidad0", "cantidad1" and "cantidad2"? I don't see those columns. — The Impaler, Oct 02 '18 at 14:49
How do you ensure that the order is correct? You don't have an `ORDER BY` clause — JDB, Oct 02 '18 at 14:49
Possible duplicate of [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — JDB, Oct 02 '18 at 14:55
@TheImpaler for example cantidad 0 = 0, cantidad 1 = 2342, cantidad 2 = 3855, cantidad 3 = 4608. etc. — Jonathan Rojas, Oct 02 '18 at 16:01

score 0 · Answer 1 · answered Oct 02 '18 at 15:12

You can use a correlated subquery to calculate the previous result:

select t.*,
       coalesce( t.cantid -
                 (select t2.cantid
                  from t t2
                  where t2.linea = t.linea and t2.fecha < t2.fecha
                  order by t2.fecha desc
                  limit 1
                 ),
                 t.cantid
                )
from t;

Subtract values on column "cantidad"

1 Answers1