Difference between revisions of "Running Total"
From SQLZOO
| Line 1: | Line 1: | ||
| − | + | <p>In this example you are shown how to get a running total in a set of results.</p> | |
| + | <p>Table 1 shows the results without the running total and Table 2 shows what we get with the running total.</p> | ||
| + | <div class="ref_section"> | ||
| + | <table class= "db_ref"> | ||
| + | <caption>Table 1</caption> | ||
| + | <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-02</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 align='left'>-10</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-05</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 align='left'>-10</td></tr> | ||
| + | </table> | ||
| + | <table class="db_ref"> | ||
| + | <caption>Table 2</caption> | ||
| + | <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-02</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 align='left'>-10</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-05</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 align='left'>-10</td></tr> | ||
| + | </table> | ||
| + | </div> | ||
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| Line 6: | Line 29: | ||
whn DATE, | whn DATE, | ||
description VARCHAR(20), | description VARCHAR(20), | ||
| − | amount | + | amount TEXT ); |
| − | 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> | ||
| Line 35: | Line 58: | ||
@accumulator:=@accumulator+amount RunningTotal | @accumulator:=@accumulator+amount RunningTotal | ||
FROM transact</div> | FROM transact</div> | ||
| − | <div class="ecomm e-oracle" style="display: none">In Oracle the following query works: | + | <div class="ecomm e-oracle" style="display: none">In Oracle the following query also works: |
SELECT whn, amount, SUM(amount) OVER (ORDER BY whn) | SELECT whn, amount, SUM(amount) OVER (ORDER BY whn) | ||
FROM transact</div> | FROM transact</div> | ||
</div> | </div> | ||
{{Hacks Ref}} | {{Hacks Ref}} | ||
Revision as of 11:11, 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 | 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 |
| 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 TEXT ); 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
