How can I sum up multiple columns in a Dataviewjs query?

[Saytama]

What I’m trying to do

I would like to create a Table query with Dataview that displays the amounts of multiple things and at the bottom displays the total amount.
I saw an answer that seemed pretty good which uses dataviewjs with the following example:

```dataviewjs
const amounts = await dv.query(`
TABLE WITHOUT ID
  file.link as Account, Amount, Payment_rate
WHERE file.folder = this.file.folder 
WHERE number(Amount)
`)

if ( amounts.successful ) {
  const sum = amounts.value.values
    .map(a => a[1])
    .reduce((tmp, curr) => tmp + curr, 0)

  amounts.value.values.push(["<span style='float: right'><strong>Total:</strong></span>", sum])
  dv.table(amounts.value.headers, amounts.value.values) 
  dv.paragraph("Total amount: " + sum)
} else
  dv.paragraph("~~~~\n" + amounts.error + "\n~~~~")

The result of that query would be the following (of course without the “Original query” part):

The Problem is that for my usecase I want to sum up multiple columns:

I’m trying to display a “Total” for each column and then a “Total amount” which is the sum of all the totals.

Things I have tried

I’ve tried to look more into dataviewjs but coding isn’t really transparent to me. Then I tried to play with the given example myself, but I couldn’t figure it out.

I’ve also tried to look up similar solutions with the normal Dataview, but they didn’t seem to do what I want. If there is a solution for Dataview, I would of course happily take that as well. It’s not important to me if the solution involves Dataview, or Dataviewjs or anything else, really.

Any help is appreciated.

[holroy]

I’m running out of daylight, and got too much to do before going to bed, but check out the last query in the answer below. There I do sum up multiple columns and add them as a separate row under the other columns.

Maybe it’ll help you on your way?

Ad-hoc tracking in Dataview of games won

Hi there @anon63144152, here are some queries for you to ponder over, and maybe spend even more hours on studying! ## Base query ```dataview TABLE P1, P2 WHERE file.folder = this.file.folder AND file != this.file ``` ## Grouped by file ```dataview TABLE rows.P1, flat(rows.P1), sum(flat(rows.P1)), rows.P2, flat(rows.P2), sum(flat(rows.P2)) WHERE file.folder = this.file.folder WHERE P1 OR P2 GROUP BY file.name as GamesPlayed ``` ## Every game with game count(?) ```dataview TABLE …

[AlanG]

In this part:

@Saytama

  const sum = amounts.value.values
    .map(a => a[1])
    .reduce((tmp, curr) => tmp + curr, 0)

Instead of selecting a single value using map, just delete that line and sum up the columns in the next line (columns 1 and 2, as numbering starts from zero):

  const sum = amounts.value.values
    .reduce((tmp, curr) => tmp + curr[1] + curr[2], 0)

[Saytama]

Thank You! That definitely helped a lot!
But ideally I want to have a “Total” under each column and then the “Total amount” to display the sum of all the totals.

Your code helped me understand what I have to do roughly, and I think I could live with the current result, but there is one problem:

const sum = amounts.value.values
		.reduce((tmp, curr) => tmp + curr[1], 0)

	const sum2 = amounts.value.values
		.reduce((tmp,curr) => tmp + curr[2], 0)

	const sum3 = amounts.value.values
		.reduce((tmp,curr) => tmp + curr[3], 0)

	amounts.value.values.push(["<span style='float: right'><strong>Total:</strong></span>", sum])

	amounts.value.values.push(["<span style='float: right'><strong>Total:</strong></span>", sum2])

	amounts.value.values.push(["<span style='float: right'><strong>Total:</strong></span>", sum3])

	dv.table(amounts.value.headers, amounts.value.values)
	dv.paragraph("Total amount: " + (sum + sum2 + sum3))
}   else 
	dv.paragraph("~~~~\n" + amounts.error + "\n~~~~")

As you can see I managed to get seperate Totals for each column and a Total amount that sums them up, but the totals are all just under the “Amount” column.
Is there a way to change their position to be under the “Amount2” and “Amount3” column.

But regardless of if I can get that last thing fixed, thank you for helping me!

[RaydioDroid]

I did it. Try this

if ( amounts.successful ) {
  const sum1 = amounts.value.values
    .reduce((tmp, curr) => tmp + curr[1], 0)
    
  const sum2 = amounts.value.values
    .reduce((tmp, curr) => tmp + curr[2], 0)
    
  const sum3 = amounts.value.values
    .reduce((tmp, curr) => tmp + curr[3], 0)

  amounts.value.values.push(["<span style='float: left'><strong>Total:</strong></span>", sum1, sum2, sum3])

  dv.table(amounts.value.headers, amounts.value.values)