Help with sum and average inline queries

I have author pages in my vault, and I’m trying to add inline queries that will show me the number of pages I’ve read by that author, as well as the average rating for the books I’ve read by that author. I’ve been able to accomplish this using dataview tables, but the result is a single line that includes extra artifacts as a result of the table format. I would prefer to use inline queries to get a single number returned, but haven’t been able to get my queries to work (admittedly, I am not well versed in javascript).

What I’m trying to do

My book pages have these Obsidian properties (amongst others):

author
pages
rating

On my author page, I have 3 queries where I’m trying to return the following data points, along with the code I’m currently using:

  • Pages Read (by author)
LIST " &nbsp" + sum(rows.pages)
WHERE pages AND author = [[Shari Lapena]]
GROUP BY Total
  • Average Rating (by author)
LIST " &nbsp" + round(sum(rows.rating) / length(rows.rating), 2)
WHERE rating AND author = [[Shari Lapena]]
GROUP BY Total
  • Total number of books read (by that author) note: I have an inline query for this and it works beautifully; no assistance needed with this one
$= dv.pages("#book").where(p => p.author?.path?.includes("Shari Lapena")).length

I’m nesting my queries within callouts, and this is what it looks like:

Things I have tried

I’ve done a lot of googling, and based on what I’ve found I have played around with versions of this to get the total number of pages read by the author:

$=dv.pages("#book").pages.where(p => p.author?.path?.includes("Shari Lapena")).values.reduce((a, b)=>a+b,0)

This code does not return an error, but the result is 0 and it should be 1310. I am also not sure how to modify this code to get an average rating; I have not found any inline queries that produce an average calculation.

I am not sure what else to try, at this point.

I appreciate any assistance.

You’ve most likely just got a sequencing error in your statement, so let us break up what you’ve actually asked for in this line:

$=dv.pages("#book").pages.where(p => p.author?.path?.includes("Shari Lapena")).values.reduce((a, b)=>a+b,0)
  • dv.pages("#book") – Look within all pages, and find those tagged with #book. Now the queue contains all those pages
  • .pages – From the queue, extract the variable pages, and now the queue contains just the pages variable corresponding to your books
  • .where(p => ... ) – This tries to look in the elements of the queue, and if that element has an author.path check whether that is the given author. However, Our queue now only contains the pages variable, which doesn’t have any author, so nothing is returned back to the queue
  • .values.reduce(...) – From the empty queue, try to extract the values variables, and use reduce() on those values to sum them together…

In short, try moving the .where() clause up above the pages clause, and you’ll most likely see a better result. I’m not quite sure whether that .values is needed or not, so you might need to remove that too.

Thank you…your explanation is super helpful. Unfortunately, I’m still unable to return a result; here’s what I’ve found:

This code

$=dv.pages("#book").where(p => p.author?.path?.includes("Shari Lapena")).values.reduce((a, b)=>a+b,0).pages

gives me a result of:
-

and this code:

$=dv.pages("#book").where(p => p.author?.path?.includes("Shari Lapena")).reduce((a, b)=>a+b,0).pages

gives me this error:

Hmm… Maybe we’re looking at this from the wrong perspective. Maybe it’s not the query which fails, but just the .reduce() function, and that it’s trying to add strings together. So try the following first:

`$=dv.pages("#book").pages.where(p => p.author?.path?.includes("Shari Lapena")).values.reduce((a, b) => a + parseInt(b), 0) `

If that fails try to do the following, and see whether that returns anything:

```dataviewjs
dv.list(
  dv.pages("#book")
    .where(p => p.author?.path?.includes("Shari Lapena"))
    .pages
)
```

If your query is correct, that should list the pages value from the corresponding pages. If it doesn’t, you need to rework that query until it does return those values. After that, the following query should return the sum:

```dataviewjs
dv.span(
  dv.pages("#book")
    .where(p => p.author?.path?.includes("Shari Lapena"))
    .pages
    .values.reduce( (a, b) => a + parseInt(b), 0 )
)
```

Please correct this query according to modifications done in the previous query, if needed. If that now returns the wanted value, you could ditch the dv.span(), concatenate everything on one line and use that as an inline query. If not, please report back on what you got in the various steps here, and possibly include some of the files which of this author displaying how you’re setting the author and the pages property.

1 Like

You’re a genius. The first query returned zero, but the second two worked. I removed the dv.span from the final query to get this, which works:

`$=dv.pages("#book").where(p => p.author?.path?.includes("Shari Lapena")).pages.values.reduce( (a, b) => a + parseInt(b), 0 )`

Thank you so much!!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.