Difference between revisions of "Running Total"
| Line 4: | Line 4: | ||
<table class= "db_ref"> | <table class= "db_ref"> | ||
<caption>Table 1</caption> | <caption>Table 1</caption> | ||
| − | <tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>''' | + | <tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''moneyIN'''</th><th>'''moneyOUT'''</th><th>'''balance'''</tr> |
<tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td></tr> | <tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td></tr> | ||
<tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | <tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | ||
| Line 15: | Line 15: | ||
<caption>Table 2</caption> | <caption>Table 2</caption> | ||
<tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''amount'''</th></tr> | <tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''amount'''</th></tr> | ||
| − | <tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td></tr> | + | <tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td><td></td><td>50</td></tr> |
| − | <tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | + | <tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>40</td></tr> |
| − | <tr><td align='left'>2006-11-03</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | + | <tr><td align='left'>2006-11-03</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>30</td></tr> |
| − | <tr><td align='left'>2006-11-04</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | + | <tr><td align='left'>2006-11-04</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>20</td></tr> |
| − | <tr><td align='left'>2006-11-05</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | + | <tr><td align='left'>2006-11-05</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>10</td></tr> |
| − | <tr><td align='left'>2006-11-06</td><td align='left'>Company Store</td><td align='left'>-10</td></tr> | + | <tr><td align='left'>2006-11-06</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>0</td></tr> |
</table> | </table> | ||
</div> | </div> | ||
Revision as of 12:59, 2 August 2012
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.
| whn | description | moneyIN | moneyOUT | balance |
|---|---|---|---|---|
| 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 |
| whn | description | amount | ||
|---|---|---|---|---|
| 2006-11-01 | Wages | 50 | 50 | |
| 2006-11-02 | Company Store | -10 | 40 | |
| 2006-11-03 | Company Store | -10 | 30 | |
| 2006-11-04 | Company Store | -10 | 20 | |
| 2006-11-05 | Company Store | -10 | 10 | |
| 2006-11-06 | Company Store | -10 | 0 |
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
