kwv84 kwv84 - 1 month ago 10
SQL Question

Create running tot with OVER in SQL Server 2014

In SQL Sever 2014 I want to create a column with a running total with partition, but I can't get it to work. This is what I have:

SELECT
a.ArtikelCode
,a.Omschrijving
,o.OrderNr
,o.Datum
,r.ProdItem
,o.Aantal
,(SUM(Aantal) OVER(PARTITION BY r.ProdItem ORDER BY r.ProdItem ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS Telling
FROM Artikel a
LEFT JOIN Receprt r ON r.ReqItem = a.ArtikelCode
LEFT JOIN [Order] o ON o.ArtikelCode = r.ProdItem
WHERE LEFT(a.ArtikelCode,1) = 'M'
GROUP BY r.ProdItem, o.ArtikelCode, a.ArtikelCode, o.Datum, o.Aantal, a.Omschrijving, o.OrderNr
ORDER BY a.ArtikelCode, r.ProdItem, o.datum


This gives me the following result:
enter image description here

As you can see, the column "Telling" does a sum on the "Aantal" column. For ArtikelCode "M01" And ProdItem "ART01" that goes well. But at record 8 it is still summing up. What I want is that it starts over again in record 8 with 2 and, In record 9 it should be 3 and in record 10 the sum in column "Telling" should be 10

Answer Source

Think you also want to partition by ArtikelCode. I think you intend to order by datum as well . . . and the rows clause is unneeded because it is there by default:

 SUM(Aantal) OVER (PARTITION BY a.ArtikelCode, r.ProdItem
                   ORDER BY o.datum
                  ) as Telling