Difference between revisions of "Running Total"

From SQLZOO
Jump to: navigation, search
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>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'>
 
<source lang='sql' class='def'>
 
SELECT w AS dte, d AS description,  
 
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
 
     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:

Revision as of 14:21, 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
whndescriptionmoneyINmoneyOUTbalance
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

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 cshIN,
   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)
     GROUP BY x.whn, x.description, x.amount) t
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense