Running Total

In this example you are shown how to get a running total in a set of results.

Table 1 shows the results without the running total and Table 2 shows what we get with the running total.

Table 1
whn	description	amount
2006-11-01	Wages	50
2006-11-02	Company Store	-10
2006-11-03	Company Store	-10
2006-11-04	Company Store	-10
2006-11-05	Company Store	-10
2006-11-06	Company Store	-10

Table 2
whn	description	amount
2006-11-01	Wages	50
2006-11-02	Company Store	-10
2006-11-03	Company Store	-10
2006-11-04	Company Store	-10
2006-11-05	Company Store	-10
2006-11-06	Company Store	-10

schema:scott

DROP TABLE transact

 CREATE TABLE transact(
  whn DATE,
  description VARCHAR(20),
  amount INTEGER );
INSERT INTO transact VALUES (2006-11-01,'Wages',50);
INSERT INTO transact VALUES (2006-11-02,'Company Store',-10);
INSERT INTO transact VALUES (2006-11-03,'Company Store',-10);
INSERT INTO transact VALUES (2006-11-04,'Company Store',-10);
INSERT INTO transact VALUES (2006-11-05,'Company Store',-10);
INSERT INTO transact VALUES (2006-11-06,'Company Store',-10);

To calculate a running total a table needs to be joined to itself, each version can be called table x and table y.

SELECT x.whn, x.description, x.amount, 
SUM(y.amount) AS balance
FROM transact x JOIN transact y ON (x.whn>=y.whn)
GROUP BY x.whn, x.description, x.amount

If you want the money IN and money OUT to be separate a CASE expression can be used.

SELECT w AS dte, d AS description, 
CASE WHEN (a>=0) THEN a ELSE NULL END AS moneyIN,
CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
balance FROM
(SELECT x.whn AS w, x.description AS d, 
 x.amount AS a, SUM(y.amount) AS balance
FROM transact x JOIN transact y ON (x.whn>=y.whn)
GROUP BY x.whn, x.description, x.amount) t

Running Total

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense