Difference between revisions of "Running Total"

From SQLZOO
Jump to: navigation, search
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
Keep a running total
+
<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><th>'''balance'''</th></tr>
 +
<tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td><td>50</td></tr>
 +
<tr><td align='left'>2006-11-02</td><td align='left'>Company Store</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><td>30</td></tr>
 +
<tr><td align='left'>2006-11-04</td><td align='left'>Company Store</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><td>10</td></tr>
 +
<tr><td align='left'>2006-11-06</td><td align='left'>Company Store</td><td align='left'>-10</td><td>0</td></tr>
 +
</table>
 +
</div>
 
<div class='ht'>
 
<div class='ht'>
 
<div class=params>schema:scott</div>
 
<div class=params>schema:scott</div>
 
<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 VARCHAR(20),
+
   whn DATE,
 
   description VARCHAR(20),
 
   description VARCHAR(20),
−
   amount INTEGER;
+
   amount INTEGER );
 
INSERT INTO transact VALUES ('2006-11-01','Wages',50);
 
INSERT INTO transact VALUES ('2006-11-01','Wages',50);
−
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
+
INSERT INTO transact VALUES ('2006-11-02','Company Store',-10);
−
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
+
INSERT INTO transact VALUES ('2006-11-03','Company Store',-10);
−
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
+
INSERT INTO transact VALUES ('2006-11-04','Company Store',-10);
−
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
+
INSERT INTO transact VALUES ('2006-11-05','Company Store',-10);
−
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
+
INSERT INTO transact VALUES ('2006-11-06','Company Store',-10);
 
</source>
 
</source>
 
<div>
 
<div>
Line 18: Line 41:
 
version can be called table x and table y.</p>
 
version can be called table x and table y.</p>
 
  SELECT x.whn, x.description, x.amount,  
 
  SELECT x.whn, x.description, x.amount,  
−
SUM(y.amount) AS balance
+
        SUM(y.amount) AS balance
−
FROM transact x JOIN transact y ON (x.whn>=y.whn)
+
  FROM transact x
−
GROUP BY x.whn, x.description, x.amount
+
  JOIN transact y ON (x.whn>=y.whn)
−
<p>If you want the money IN and money OUT to be separate a CASE expression can be used.</p>
+
  GROUP BY x.whn, x.description, x.amount
 +
<p>[[Credit_debit |Learn how to split the amount table into cshIN and cshOUT]]</p>
 
</div>
 
</div>
−
<source lang='sql' class='def'>SELECT w AS dte, d AS description,  
+
<source lang='sql' class='def'>
−
CASE WHEN (a>=0) THEN a ELSE NULL END AS moneyIN,
+
SELECT w AS dte, d AS description, a, balance
−
CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
+
  FROM
−
balance FROM
+
  (SELECT x.whn AS w, x.description AS d,  
−
(SELECT x.whn AS w, x.description AS d,  
+
          x.amount AS a, SUM(y.amount) AS balance
−
x.amount AS a, SUM(y.amount) AS balance
+
    FROM transact x
−
FROM transact x JOIN transact y ON (x.whn>=y.whn)
+
    JOIN transact y ON (x.whn>=y.whn)
−
GROUP BY x.whn, x.description, x.amount) t</source>
+
    GROUP BY x.whn, x.description, x.amount) t</source>
 
<div class="ecomm e-mysql" style="display: none">In MySQL the following query also works:
 
<div class="ecomm e-mysql" style="display: none">In MySQL the following query also works:
 
SELECT whn, description, amount,
 
SELECT whn, description, amount,
 
@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}}

Latest revision as of 15:08, 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.

Table 1
whndescriptionamount
2006-11-01Wages50
2006-11-02Company Store-10
2006-11-03Company Store-10
2006-11-04Company Store-10
2006-11-05Company Store-10
2006-11-06Company Store-10
Table 2
whndescriptionamountbalance
2006-11-01Wages5050
2006-11-02Company Store-1040
2006-11-03Company Store-1030
2006-11-04Company Store-1020
2006-11-05Company Store-1010
2006-11-06Company Store-100
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

Learn how to split the amount table into cshIN and cshOUT

SELECT w AS dte, d AS description, a, 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
SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

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 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page

Retrieved from "http://sqlzoo.net/w/index.php?title=Running_Total&oldid=3015"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense