Help with dataview group and sum

[giannis]

I have data in the following format in daily notes:

Day 1

Item:: Coffee - 3.00

Day 2

Item:: Coffee - 2.80
Item:: Juice - 3.30

Day 3

Item:: Coffee - 2.80
Item:: Donuts - 1.8
Item:: Burger - 4.60

Day 4

Item:: Coffee - 3.20
Item:: Donuts - 1.80

Day 5

Item:: Donuts - 1.80

With the following query I’m getting a table that lists all items.

TABLE WITHOUT ID split(Item, " - ")[0] AS Item, number(Item) as "Amount"
WHERE Item
FLATTEN Item
SORT file.ctime

I can’t figure out how to get a list of Grouped Sums by Item (Coffee,Donuts etc), I would like also to format all output number with two decimals.

Any ideas ?

[holroy]

I built up the same test set, and executed this query on it:

```dataview
TABLE item, i, j
WHERE item
SORT file.ctime
FLATTEN item as i
FLATTEN list(split(i, "\s*-\s*")) as j
```

Which gave me this output:

Here we can see that the initial list of items is shown in second column and after that has been flattened into i, we see a single item in the third column (with the corresponding list repeated in the second column). This is further split into a list j which holds the distinct entries (in the fourth column). I’ve used a slightly more accepting regex to accommodate for any amount of spaces around the dash to do the split.

Finding our bits and pieces

Now we’re ready to tackle the main issue, with the knowledge that j[0] is our item (or thingy, as I’ve called it in the query below), and the cost from number(j[1]) translated into a number to make the calculations easier.

Your first question then simply becomes a GROUP BY thingy, which will move all other fields into the rows object, so now we can do sum(rows.cost) as sum to get the sum of costs for that particular item.

You also wanted to present the output with two decimals, which is actually harder than it looks within dataview. You can round the answer to two decimals using round(sum, 2) as sum2, but when it’s presented and the last digit is zero it’s dropped.

The most reliable way to get two decimals that I’ve found is to multiply by 100, convert to a string, and “manually” insert the decimal point at the correct position. Aka regexreplace(string(round(sum*100)), "(..)$", ".$1") as sum3.

Here is a query showing all those sums, and doing these operations:

```dataview
TABLE WITHOUT ID thingy, sum, sum2, sum3
WHERE item
SORT file.ctime
FLATTEN item as i
FLATTEN list(split(i, "\s*-\s*")) as j
FLATTEN j[0] as thingy
FLATTEN number(j[1]) as cost
GROUP BY thingy
FLATTEN sum(rows.cost) as sum
FLATTEN round(sum, 2) as sum2
FLATTEN regexreplace(string(round(sum*100)), "(..)$", ".$1") as sum3
```

Which displays as:

This is the verbose version of this query, and you most likely don’t care about all those intermediate sums and so on, so the most condensed version, but still keeping the naming of the cost:

```dataview
TABLE WITHOUT ID key as Item, sum as "Cost"
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
```

Which then displays as:

[giannis]

Thanks a lot @holroy this is what I was looking for.
Is it possible to have the total Cost.

[holroy]

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:

Automating Timesheet Help - Query embedded fields and sum hours across all files in current folder

I kind of triggered myself with these sum across the board, so here is a rather intense query doing three different sums in two tables. Before I present the query, and an output example, lets re-iterate on the base for the query, and which totals we’re going to build. Base query requirements (with three different sum variants) The base data is a standard DQL query holding the columns: project, date, tasks, hours. In all the sums below, we’re aiming to sum the hours in various ways, and the tab…

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:

[giannis]

Thanks a lot again, I add the dv.paragraph("Total amount: " + (Math.round(sum*100)/100).toFixed(2)) after dv.table… in order to get a simple line of total.

[giannis]

I found something weird, when I use negatives values (occasionally) the group disappears from the output.