-1

I have two tables, orders and stock, I need to fill the order with the stock available, but can I make a running sum works

CREATE TABLE `PEDIDOS` ( `N_PEDIDO` INTEGER, `COD_MATERIAL` INTEGER, `CAJAS_PEDIDOS` INTEGER )
CREATE TABLE `STOCK` ( `COD_MATERIAL` INTEGER, `CAJAS` INTEGER )

sql 

SELECT      PEDIDOS.N_PEDIDO, 
            PEDIDOS.COD_MATERIAL, 
            PEDIDOS.CAJAS_PEDIDOS, 
            STOCK.CAJAS,
            sum(PEDIDOS.CAJAS_PEDIDOS) - STOCK.CAJAS as 'CAJAS_ASIGNADAS'
FROM        PEDIDOS 
INNER JOIN  STOCK
ON          PEDIDOS.COD_MATERIAL = STOCK.COD_MATERIAL
GROUP BY    PEDIDOS.COD_MATERIAL, PEDIDOS.N_PEDIDO
ORDER BY    PEDIDOS.N_PEDIDO, PEDIDOS.COD_MATERIAL;

I expect a result like

Order    Material   Qty Ord  Stock   Balance
20001      100001   500      300    -200
20001      100002   200      200      0
20001      100003    40       50     10
20002      100001    30      300    -230
20002      100003    50       50      0

http://sqlfiddle.com/#!7/0a4d6/1

Renzo Ulloa
  • 1
  • 3
  • 1
    Your expected does not appear to actually be a running total, because the balance shown is for each row alone. Also, if you want a running total, you should tell us which column(s) determine the order for computing the total. – Tim Biegeleisen Feb 17 '19 at 03:38
  • I must fill the "cod_material" from orders tables with "cod_material" from stock tables – Renzo Ulloa Feb 17 '19 at 03:47
  • A result without a data set doesn't make much sense. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 17 '19 at 09:56
  • I'm so sorry, have you right, I miss that, this is the schema and sql http://sqlfiddle.com/#!7/0a4d6/1 – Renzo Ulloa Feb 17 '19 at 15:43

1 Answers1

0

this is the solution, maybe help someone else

http://sqlfiddle.com/#!7/0a4d6/79/0

was made in sqlite

Renzo Ulloa
  • 1
  • 3