SQL Queries: Creating a Running Total – OSKAR

This will require Builder privileges for setup and implementation.

There is no way to use an Expression column to write a JavaScript statement from within a chart that can show the running total so below are some options for how to make this work with custom SQL queries. If you have Data Hub, with ETL Jobs there is also an Add Columns transformation task where you can choose A Running Total for your column population.

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,

RunTotalTestData b

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

Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.

Creating a Running Total Column

Using a Correlated Scalar Subquery

Using a JOIN

Related articles