Subscribe to this thread
Home - General / All posts - Calculating running total of an attribute in M9
danb

2,039 post(s)
#15-Mar-23 02:14

Does anyone know how to calculate a running total of an attribute in 9? There was a transform function to do this in M8 but it would be great to have a method to achieve this in M9.

Thanks in advance.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

Dimitri


7,313 post(s)
#15-Mar-23 07:40

You can use SQL for that. The first thing to keep in mind is that like any real DBMS, tables in 9 are unordered. Records can appear in any random order. If you want to see some particular order based on a field, and thus a particular running total based on that order, you have to use ORDER BY. (Release 8 is a bit hackish from a DBMS perspective because it pretends that tables have an intrinsic order... )

OK, so suppose you have an attribute field that gives the order of each record. Suppose you have a table called Sales and there is a sales ID field that gives the order of each sale such that each ID is unique and higher ID values indicate later sales. Suppose you also have an Amount field. You want to create a table that lists all sales in order by sales ID with the Amount of each sale and a Running Total of amounts for that sale and all prior. Use a query:

SELECT T1.mfd_id, T1.Amount, Sum(T2.Amount) as RunningTotal 

FROM Sales AS T1

 INNER JOIN Sales as T2

 ON T1.mfd_id >= T2.mfd_id 

GROUP BY T1.mfd_id, T1.Amount 

ORDER BY T1.mfd_id, T1.Amount;

The above is an inner join that joins the Sales table to itself. I used the mfd_id field instead of cobbling up a fake sales ID field. I used simple field and table names so no need to bracket [ ] them. You could, of course, Style the RunningTotal field. [Oops... don't need T1.Amount in the ORDER BY...]

Attachments:
running_total.png

tjhb
10,071 post(s)
#16-Mar-23 00:55

(Also you don’t really need the ORDER BY at all, only GROUP BY. The sum depends on the ordering implicit in the join, but not on the order of records.)

danb

2,039 post(s)
#16-Mar-23 01:26

Thanks chaps. Much appreciated. I was wondering vaguely if this was the sort of thing that COLLECT is designed to deal with, but a join will be just the job. Appreciated as ever.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.