Typical problems

Correlated subqueries

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:

Console

Execute

SELECT point, date, out
FROM Outcome_o o
WHERE point = 2
ORDER BY date;

SELECT point, date, out FROM Outcome_o o WHERE point = 2 ORDER BY date;

    Scheme

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.

Console

Execute

SELECT point, date, out, 
  (SELECT SUM(out) 
   FROM Outcome_o 
   WHERE point = o.point AND date <= o.date) run_tot 
FROM Outcome_o o
WHERE point = 2
ORDER BY point, date;

SELECT point, date, out,   (SELECT SUM(out)    FROM Outcome_o    WHERE point = o.point AND date <= o.date) run_tot FROM Outcome_o o WHERE point = 2 ORDER BY point, date;

    Scheme

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

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:

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:

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.

Console

Execute

SELECT date, SUM(out) out, 
  (SELECT SUM(out) 
   FROM Outcome_o 
   WHERE date <= o.date) run_tot 
FROM Outcome_o o
GROUP BY date
ORDER BY date;

SELECT date, SUM(out) out,   (SELECT SUM(out)    FROM Outcome_o    WHERE date <= o.date) run_tot FROM Outcome_o o GROUP BY date ORDER BY date;

    Scheme

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:

Console

Execute

SELECT point, date, out, 
  (SELECT SUM(out) 
   FROM Outcome_o 
   WHERE date < o.date OR ( date = o.date AND point <= o.point)) run_tot 
FROM Outcome_o o
ORDER BY date, point;

SELECT point, date, out,   (SELECT SUM(out)    FROM Outcome_o    WHERE date < o.date OR ( date = o.date AND point <= o.point)) run_tot FROM Outcome_o o ORDER BY date, point;

    Scheme

Next page