This will require Builder privileges for setup and implementation.
Creating a Running Total Column
Using a Correlated Scalar Subquery
A scalar subquery is an expression that can return a maximum of one value, and a correlated scalar subquery returns a single value for each row of its correlated outer table set. A value list, is a list of values that can be either constants or parameterized values. However there are some limitations to what you can specify:
- In an UPDATE statement you cannot specify a correlated scalar subquery in the SET or WHERE clause.
- In a MERGE statement you cannot specify a correlated scalar subquery in the ON clause.
- In a value list you cannot specify a correlated scalar subquery.
One way to use a correlated scalar query is to fetch the running total.
SELECT a.id, a.value, (SELECT SUM(b.value)
FROM RunTotalTestData b
WHERE b.id <= a.id)
FROM RunTotalTestData a
ORDER BY a.id;
When this is run, the results are:
id value running total
-- ----- -------------
1 1 1
2 2 3
3 4 7
4 7 14
5 9 23
6 12 35
7 13 48
8 16 64
9 22 86
10 42 128
11 57 185
12 58 243
13 59 302
14 60 362
Here, along with the actual row values we have a running total. The scalar query simply fetches the sum of the value field from the rows where the ID is equal or less than the value of the current row. What happens is that the database fetches all the rows from the table and using a nested loop, it again fetches the rows from which the sum is calculated.
Using a JOIN
Another variation is to use a JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. JOINs are often visualized as venn diagrams.
SELECT a.id, a.value, SUM(b.Value)
FROM RunTotalTestData a,
WHERE b.id <= a.id
GROUP BY a.id, a.value
ORDER BY a.id;
The results are the same but the technique is a bit different, instead of fetching the sum for each row, the sum is created by using a GROUP BY clause.
For More Information:
- iDashboards Builder Manual 14 Analytics
- SQL Query Help
If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.