Running totals page 1

The correlated subquery may be used for calculation accumulation running totals the usual practical problem.

In assumption of some rows` order the running total for each row is a sum of values for some numeric column in this row and for all rows above.

In the other words, the running total for first row in the ordered set would be equal to the numeric value in this row. For any other rows the running total would be equal to sum of numeric value in this row and the running total in the previous rows.

Let`s consider task like this.

For point 2 in the Outcome_o table for each day get the summary outlay for this day and for all previous days.

Here is the query outputs information about outlays in point 2 in order of ascending of the date:

  1. SELECT point, date, out
  2. FROM Outcome_o o
  3. WHERE point = 2
  4. ORDER BY date;

point date out
2 2001-03-22 00:00:00.000 1440.00
2 2001-03-29 00:00:00.000 7848.00
2 2001-04-02 00:00:00.000 2040.00

Actually, to solve this problem we need to add one column for accumulating running total (run_tot). Correspondingly the theme, this column would be represent the correlated sub-query. In this subquery the total sum of the values in the out column would be calculated for this date and for all preceding dates.

  1. SELECT point, date, out,
  2. (SELECT SUM(out)
  3. FROM Outcome_o
  4. WHERE point = o.point AND date <= o.date) run_tot
  5. FROM Outcome_o o
  6. WHERE point = 2
  7. ORDER BY point, date;

point date out run_tot
2 2001-03-22 00:00:00.000 1440.00 1440.00
2 2001-03-29 00:00:00.000 7848.00 9288.00
2 2001-04-02 00:00:00.000 2040.00 11328.00

In fact, the usage of point 2 is dictated by wish to reduce the resulting set. If we want to get running totals for all points in the Outcome_o table, we need to close in comment this string

  1. WHERE point = 2

And if we want to get running total for whole table we need to put off the condition for point`s equality:

  1. point= o.point

But in this case we would get one total for different points which working in the same day. Here`s the such fragment from the resulting set:

point date out run_tot
1 2001-03-29 00:00:00.000 2004.00 33599.00
2 2001-03-29 00:00:00.000 7848.00 33599.00

This is not an obstacle if we understand what we want to get. If we want to get the accumulation of the outlay by days we need to exclude the point and summarize the outlay by days.

  1. SELECT date, SUM(out) out,
  2. (SELECT SUM(out)
  3. FROM Outcome_o
  4. WHERE date <= o.date) run_tot
  5. FROM Outcome_o o
  6. GROUP BY date
  7. ORDER BY date;

In the other case, we need to assign the order of the addition of the points` outlays if the dates is coincided.
For instance, let`s order it by ascending of its numbers:

  1. SELECT point, date, out,
  2. (SELECT SUM(out)
  3. FROM Outcome_o
  4. WHERE date < o.date OR ( date = o.date AND point <= o.point)) run_tot
  5. FROM Outcome_o o
  6. ORDER BY date, point;

Next page

Suggested exercises: 69, 101, 134

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.