Difference between revisions of "Running Total"

From SQLZOO
Jump to: navigation, search
Line 41: 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 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>If you want the money IN and money OUT to be separate a <code>CASE</code> expression can be used.</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,  
CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
+
      CASE WHEN (a>=0) THEN a ELSE NULL END AS moneyIN,
balance FROM
+
      CASE WHEN (a<0) THEN a ELSE NULL END AS moneyOUT,
(SELECT x.whn AS w, x.description AS d,  
+
      balance
x.amount AS a, SUM(y.amount) AS balance
+
  FROM
FROM transact x JOIN transact y ON (x.whn>=y.whn)
+
  (SELECT x.whn AS w, x.description AS d,  
GROUP BY x.whn, x.description, x.amount) t</source>
+
          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</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,

Revision as of 13:43, 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
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
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

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense