SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor

SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor

• Sample Data Used by Examples

Create a table named "Orders" and enter some sample data into it...

create table Orders
(
OrderID int identity,
OrderAmt Decimal(8,2),
OrderDate SmallDatetime
)

Select command gives the following output:

OrderID OrderAmt OrderDate
----------- ---------- --------------------
1 10.50 2003-10-11 08:00:00
2 11.50 2003-10-11 10:00:00
3 1.25 2003-10-11 12:00:00
4 100.57 2003-10-12 09:00:00
5 19.99 2003-10-12 11:00:00
6 47.14 2003-10-13 10:00:00
7 10.08 2003-10-13 12:00:00
8 7.50 2003-10-13 19:00:00
9 9.50 2003-10-13 21:00:00
All my examples will be using this table to produce the running totals, sub totals, and grand total reports shown below. Basically this table contains a number of different orders that where created over time. Each order has an ID (OrderID) which uniquely identifies each record, an order amount (OrderAmt) that holds a decimal amount for the order, and a timestamp (OrderDate) that identifies when the order was placed.

• Running Total On Each Record

This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The "Running Total" column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.
select OrderId, OrderDate, O.OrderAmt,
(select sum(OrderAmt) from Orders where OrderID <= O.OrderID) as 'Running-Total' from Orders O

On running the above query, the following rows are listed :

OrderId OrderDate OrderAmt Running-Total
----------- -------------------- ---------- -------------
1 2003-10-11 08:00:00 10.50 10.50
2 2003-10-11 10:00:00 11.50 22.00
3 2003-10-11 12:00:00 1.25 23.25
4 2003-10-12 09:00:00 100.57 123.82
5 2003-10-12 11:00:00 19.99 143.81
6 2003-10-13 10:00:00 47.14 190.95
7 2003-10-13 12:00:00 10.08 201.03
8 2003-10-13 19:00:00 7.50 208.53
9 2003-10-13 21:00:00 9.50 218.03

As you can see, there is a "Running Total" column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.

• Running Totals With Subtotals and Grand totals

In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.
select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID and convert(char(10),OrderDate,101) = convert(char(10),O.OrderDate,101)) else ' ' end as 'Sub Total', case when OrderID = (select top 1 OrderId from Orders order by OrderDate desc) then (select cast(sum(OrderAmt) as char(10)) from Orders) else ' ' end as 'Grand Total' from Orders O order by OrderID

Output from the SELECT statement looks like this:

OrderID Order Date OrderAmt Sub Total Grand Total
----------- ---------- ---------- ---------- -----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08
8 10/13/2003 7.50
9 10/13/2003 9.50 74.22 218.03
In this example the first CASE statement controls the printing of the "Sub Total' column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the "Grand Total", which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the "Grand Total".