Advanced Database – Apply a running sum for a group, over all.

In Libreoffice Base, there is no automatic option to do this in a report.

We must first create a query that we will then use to run a report.

7 Apply a running sum for a group over all

Create a new query in design view.

Add the table Job Costs twice, the reason for this will be explained shortly.

8 Create Query

Creating a query that gives a running total column is quite tricky in Base.

Switch to SQL mode and enter the code shown to the left.

This SQL code names the tables a1 and a2, then uses the SUM command to create a running total or ‘Subtotal’

The statement WHERE “a1”.”Cost”<=“a2”.”Cost” is important as this adds them up cumulatively. Go back to design view.

9 Query Logic In Design View

It may be a bit clearer to see the query logic in design view.

The Cost column is subtotalled using the SUM function, but first the Cost field is put into descending order, then the new value is added to each of the smaller values (<=a2.Cost).

This is complicated, but it’s the only way to get a cumulative total in Base.

Press F5 to see the query result.

10 Running Total

Notice that the subtotal is a running total of Cost.

The results may not be perfect, as we might prefer the Job No to be listed in ascending order.

Unfortunately, we can’t do that as we had to sort the Cost field in order to create the Subtotal.

This is the best we can do in LibreOffice Base.

11 Display The Running Sum

Now we can run a report to display the running sum.

Use the wizard to create a report.

Add the query we just created and select all 3 fields.

Click Finish to create the report.

12 Running Sum Column

A report is generated that displays the running sum column.