Difference between revisions of "Running Total"
m |
|||
| Line 4: | Line 4: | ||
<source lang=sql class='tidy'>DROP TABLE transact</source> | <source lang=sql class='tidy'>DROP TABLE transact</source> | ||
<source lang=sql class='setup'> CREATE TABLE transact( | <source lang=sql class='setup'> CREATE TABLE transact( | ||
| − | whn | + | whn DATE, |
description VARCHAR(20), | description VARCHAR(20), | ||
amount INTEGER ); | amount INTEGER ); | ||
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,01),'Wages',50); |
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,02),'Company Store',-10); |
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,03),'Company Store',-10); |
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,04),'Company Store',-10); |
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,05),'Company Store',-10); |
| − | INSERT INTO transact VALUES ( | + | INSERT INTO transact VALUES ((2006,11,06),'Company Store',-10); |
</source> | </source> | ||
<div> | <div> | ||
Revision as of 12:10, 30 July 2012
Keep a running total
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
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25 Keep a running total
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
