Difference between revisions of "Running Total"

From SQLZOO
Jump to: navigation, search
 
(5 intermediate revisions by the same user not shown)
Line 14: Line 14:
 
<table class="db_ref">
 
<table class="db_ref">
 
<caption>Table 2</caption>
 
<caption>Table 2</caption>
<tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''moneyIN'''</th><th>'''moneyOUT'''</th><th>'''balance'''</th></tr>
+
<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></td><td>50</td></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></td><td align='left'>-10</td><td>40</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></td><td align='left'>-10</td><td>30</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></td><td align='left'>-10</td><td>20</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></td><td align='left'>-10</td><td>10</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></td><td align='left'>-10</td><td>0</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>
 
</table>
 
</div>
 
</div>
Line 42: Line 42:
 
  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
 +
  JOIN transact y ON (x.whn>=y.whn)
 
   GROUP BY x.whn, x.description, x.amount
 
   GROUP BY x.whn, x.description, x.amount
<p>If you want the money IN and money OUT to be separate a <code>CASE</code> expression can be used.</p>
+
<p>[[Credit_debit |Learn how to split the amount table into cshIN and cshOUT]]</p>
 
</div>
 
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT w AS dte, d AS description,  
+
SELECT w AS dte, d AS description, a, balance
      CASE WHEN (a>=0) THEN a ELSE NULL END AS moneyIN,
 
      CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
 
      balance
 
 
   FROM
 
   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 JOIN transact y ON (x.whn>=y.whn)
+
     FROM transact x
 +
    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 also 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