Getting running total in sql

Question

How can i calculate the running total from below query

Query

SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
  ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
  ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
  ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
  ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
  ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
  ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
         dbo.Restaurants.Name,
         dbo.PurchaseInvoices.PurchaseInvoiceDate,
         dbo.PurchaseInvoices.PurchaseInvoiceNo,
         Suppliers.SupplierName
ORDER BY dbo.PurchaseInvoices.PurchaseInvoiceNo

Query Out Put

+------------+--------------+---------------+-----------------+--------+
| Invoice No | Invoice Date | Supplier Name | Restaurant Name |  Debit |
+------------+--------------+---------------+-----------------+--------+
|          1 | 8/26/2016    | supplier1     | restaurant 1    |  92.00 |
|          2 | 8/27/2016    | supplier1     | restaurant 2    |  47.00 |
+------------+--------------+---------------+-----------------+--------+

The desired out put should be like below

+------------+--------------+---------------+-----------------+--------+---------+
| Invoice No | Invoice Date | Supplier Name | Restaurant Name |  Debit | Balance |
+------------+--------------+---------------+-----------------+--------+---------+
|          1 | 8/26/2016    | supplier1     | restaurant 1    |  92.00 |   92.00 |
|          2 | 8/27/2016    | supplier1     | restaurant 2    |  47.00 |  139.00 |
+------------+--------------+---------------+-----------------+--------+---------+

How can i achieve this? to get running total.

Pரதீப் · Accepted Answer · 2016-08-27T08:52:13.617

You can use APPLY operator or Correlated sub-query to find running total

;with result as 
(
SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
  ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
  ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
  ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
  ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
  ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
  ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
         dbo.Restaurants.Name,
         dbo.PurchaseInvoices.PurchaseInvoiceDate,
         dbo.PurchaseInvoices.PurchaseInvoiceNo,
         Suppliers.SupplierName
)
SELECT * 
FROM   result a 
       OUTER apply (SELECT Sum([debit]) 
                    FROM   result b 
                    WHERE  a.[invoice no] >= b.[invoice no]) cs (balance)

Unfortunately you are still in Sql Server 2008, in Sql Server 2012+ we have Sum() Over(Order by) aggregate window function to calculate running which is way faster than above method

Update: For SQL SERVER 2012+ use this

;with result as
(
SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
  ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
  ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
  ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
  ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
  ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
  ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
         dbo.Restaurants.Name,
         dbo.PurchaseInvoices.PurchaseInvoiceDate,
         dbo.PurchaseInvoices.PurchaseInvoiceNo,
         Suppliers.SupplierName
)
SELECT *, sum(Debit) over(Order by [invoice no]) as balance
FROM   result a

@Ayman - Then why is your question tagged with `SQL SERVER 2008` :o — Pரதீப், Aug 27 '16 at 08:39
same Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'result'. — Ayman, Aug 27 '16 at 08:53

Vincent Pan · Answer 2 · 2016-08-27T10:31:41.480

0

Use the SUM() OVER( ORDER BY ROWS UNBOUNDED PRECEDING AND CURRENT ROW) Try the below select statement:

SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit,
  CONVERT(decimal(9,2), SUM(SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) OVER(ORDER BY dbo.PurchaseInvoices.PurchaseInvoiceNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ) AS Balance
FROM ...

edited Aug 27 '16 at 10:31

answered Aug 27 '16 at 08:33

Vincent Pan

246
1
7

Question is tagged with `SQL SERVER 2008` which does not support `sum() over(Order by)` – Pரதீப் Aug 27 '16 at 08:37
I got this error Incorrect syntax near the keyword 'FROM'. – Ayman Aug 27 '16 at 08:42
I've added an edit, I think it was missing a closing bracket for the `CONVERT()`. Tell me if it does/ doesn't work/ if you are using `SQL Server 2008`. – Vincent Pan Aug 27 '16 at 10:32

Getting running total in sql

2 Answers2