Difference between revisions of "Running Total"

From SQLZOO
Jump to: navigation, search
(Created page with "Keep a running total <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE transact</source> <source lang=sql class='setup'> CREATE TA...")
 
Line 17: Line 17:
 
<p>To calculate a running total a table needs to be joined to itself, each
 
<p>To calculate a running total a table needs to be joined to itself, each
 
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, SUM(y.amount) AS balance
+
  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 JOIN transact y ON (x.whn>=y.whn)
 
  GROUP BY x.whn, x.description, x.amount
 
  GROUP BY x.whn, x.description, x.amount

Revision as of 11:54, 30 July 2012

Keep a running total

schema:scott
DROP TABLE transact
 CREATE TABLE transact(
  whn VARCHAR(20),
  description VARCHAR(20),
  amount INTEGER;
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-01','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-01','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-01','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