Help with dataview group and sum

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:

1 Like