If you have ever had the
need to show detailed data of individual transactions and also keep a running
total, subtotals, and grand total columns at the same time, but were not
exactly sure how to tackle the problem then this article might help. In this
article I will show you a few different techniques for calculating and summing
information on multiple rows without using a cursor. The techniques I will show
you will just use a basic SELECT statement. Of course, the calculations of
the running total, subtotals and grand total will be done using other SQL
clauses and functions like SUM and CASE.
Sample Data Used by Examples
Prior to showing you my
examples, I will first need to establish a set of test data, which all my
examples will use. My test data will consist of an “Orders” table with the
following format:
create table Orders
(OrderID int identity,
OrderAmt Decimal(8,2),
OrderDate SmallDatetime)
I’ve populated this test Orders
table with the following set of records:
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) 'Running Total' from Orders O
When I run this query
against my Orders table I get the following results:
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 Total for Each
OrderDate
This example is similar to
the one above, but I will calculate a running total for each record, but only
if the OrderDate for the records are on the same date. Once the OrderDate is for
a different day, then a new running total will be started and accumulated for
the new day. Here is the code to accomplish this:
select O.OrderId,
convert(char(10),O.OrderDate,101) as ‘Order Date’,
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
‘Running Total’
from Orders O
order by OrderID
Here are the results returned
from the query using my sample Orders Table:
OrderId Order Date OrderAmt Running Total
———– ———- ———- —————
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14 47.14
7 10/13/2003 10.08 57.22
8 10/13/2003 7.50 64.72
9 10/13/2003 9.50 74.22
Note that the “Running Total”
starts out with a value of 10.50, and then becomes 22.00, and finally becomes
23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But
when OrderID 4 is displayed the running total is reset, and the running total
starts over again. This is because OrderID 4 has a different date for its OrderDate,
then OrderID 1, 2, and 3. Calculating this running total for each unique date is
once again accomplished by using a correlated sub query, although an extra WHERE
condition is required, which identified that the OrderDate’s on different
records need to be the same day. This WHERE condition is accomplished by using
the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
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”.
Conclusion
Hopefully these examples
will help you understand different methods that can be used to calculate
running totals, sub totals, and a grand total. As you can see you don’t need
to use a cursor to calculate these different totals. With the creative use of
correlated sub queries and other SELECT clauses like CASE you can easily create
all these different totals. Next time you need to calculate totals consider
using one of these non-cursor based solutions.
