Running Total - Revision history

Connor at 15:08, 2 August 2012

2012-08-02T15:08:23Z

Connor at 15:07, 2 August 2012

2012-08-02T15:07:59Z

Connor at 15:02, 2 August 2012

2012-08-02T15:02:49Z

Connor at 14:36, 2 August 2012

2012-08-02T14:36:25Z

Connor at 14:21, 2 August 2012

2012-08-02T14:21:14Z

Connor at 14:18, 2 August 2012

2012-08-02T14:18:27Z

Connor at 13:01, 2 August 2012

2012-08-02T13:01:02Z

Connor at 12:59, 2 August 2012

2012-08-02T12:59:44Z

Andr3w at 12:43, 2 August 2012

2012-08-02T12:43:37Z

Andr3w at 12:37, 2 August 2012

2012-08-02T12:37:43Z

@@ Line 56: / Line 56: @@
       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:
- 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:
- 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>
 {{Hacks Ref}}

@@ Line 56: / Line 56: @@
       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:
-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:
-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>
 {{Hacks Ref}}

@@ Line 58: / Line 58: @@
 SELECT whn, description, amount,
 @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:
 SELECT whn, amount, SUM(amount) OVER (ORDER BY whn)
-FROM transact</div>
+  FROM transact</div>
 </div>
 {{Hacks Ref}}

@@ Line 14: / Line 14: @@
 <table class="db_ref">
 <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>
 </div>
@@ Line 45: / Line 45: @@
     JOIN transact y ON (x.whn>=y.whn)
     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>
 <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 cshIN,
-   CASE WHEN (a<0) THEN a ELSE NULL END AS cshOUT,
-     balance
    FROM
    (SELECT x.whn AS w, x.description AS d,

@@ Line 42: / Line 42: @@
   SELECT x.whn, x.description, x.amount,
          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
 <p>If you want the money IN and money OUT to be separate a <code>CASE</code> expression can be used.</p>
@@ Line 48: / Line 49: @@
 <source lang='sql' class='def'>
 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 cshIN,
-     CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
+   CASE WHEN (a<0) THEN a ELSE NULL END AS cshOUT,
       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)
+      FROM transact x
       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:

@@ Line 4: / Line 4: @@
 <table class= "db_ref">
 <caption>Table 1</caption>
-<tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''moneyIN'''</th><th>'''moneyOUT'''</th><th>'''balance'''</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-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
@@ Line 14: / Line 14: @@
 <table class="db_ref">
 <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'>'''moneyIN'''</th><th>'''moneyOUT'''</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-02</td><td align='left'>Company Store</td><td></td><td align='left'>-10</td><td>40</td></tr>

@@ Line 30: / Line 30: @@
    description VARCHAR(20),
    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-02,'Company Store',-10);
+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-03','Company Store',-10);
-INSERT INTO transact VALUES (2006-11-04,'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-05','Company Store',-10);
-INSERT INTO transact VALUES (2006-11-06,'Company Store',-10);
+INSERT INTO transact VALUES ('2006-11-06','Company Store',-10);
 </source>
 <div>