You’ve got two options (at least), and that is either to do the sum in a separate query below the other, or you could trigger the query from within a dataviewjs script which can add the sum in a new last row. A long (and somewhat hairy) description on the latter approach can be found in the post below:
But we can try to adapt it here for both cases.
Using a separate table
Instead of grouping on the item themselves, we can do a general grouping, using GROUP BY true
, and end up with something like:
```dataview
TABLE WITHOUT ID sum as "Total cost"
WHERE file.folder = this.file.folder
WHERE item
SORT file.ctime
FLATTEN item as i
FLATTEN list(split(i, "\s*-\s*")) as j
FLATTEN number(j[1]) as cost
GROUP BY true
FLATTEN regexreplace(string(round(sum(rows.cost)*100)), "(..)$", ".$1") as sum
```
Essentially I’ve only changed the grouping, and removed the key from the presentation. And we end up with:
Extending the table using dataviewjs
Even though it kind of looks overwhelming, bear in mind that for later re-use all you need to do is to test and verify your query to show the correct stuff, and then replace it within the call to await dv.query(` … `)
, and also correct which column you want to sum (with 0 for the first column).
```dataviewjs
const result = await dv.query(`
TABLE WITHOUT ID key as Item, sum as "Cost"
WHERE file.folder = this.file.folder
WHERE item
SORT file.ctime
FLATTEN item as i
FLATTEN list(split(i, "\s*-\s*")) as j
FLATTEN number(j[1]) as cost
GROUP BY j[0]
FLATTEN regexreplace(string(round(sum(rows.cost)*100)), "(..)$", ".$1") as sum
`)
if ( result.successful ) {
const values = result.value.values
const sum = values.map(v => v[1]) // <-- Column to sum
.reduce((acc, tmp) => acc + parseFloat(tmp), 0.0)
values.push([
`_<span style="float: right">Total</span>_`,
(Math.round(sum*100)/100).toFixed(2),
])
dv.table(result.value.headers, values)
} else
dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```
Also if you want to adapt this to some other query with a different column count, be sure to add something to the corresponding entries in the values.push()
line. Simply add "",
on a line of its own to insert an empty column.
Using the query above we get this output: