Can Dataview count occurrences of a Value (Author name) and sort by that number?

I can figure out how to do this if I wanted to query for specific authors, e.g.

WHERE Author = Stephen King

Or if going the DataviewJS route, I could do:

dv.pages('"Book Log"').where(p => p.Author == "Stephen King")

However what I’d like to do now is have Dataview count the occurrences of any author in the Author: field and then sort by it. That way if I read 5 books by a new author I don’t have to enter that new query but the table would just automatically include it in the sorting.

My googling/searching has been fruitless, as I can’t think of any other term besides frequency and occurrences to look for this.

Thanks :slight_smile:

The term you’re looking for is most likely “group by” and then doing length on the various results. I could’ve written an example query, but I don’t know enough about your data structure to do so. Like, is every book a single note? Is there only ever one author to a book?

I just looked it up in the documentation, I don’t understand what Group by does.

There are occasionally multiple authors. Does that make a difference?

Author: Neal Stephenson; Nicole Galland
Alias: The Rise and Fall of D.O.D.O. (D.O.D.O. 1) 
Year: 2017
Medium: Audiobook
Length: 25
Rating: 6
DateStarted: 2020-05-24
DateFinished: 2020-07-16
Tags: ScienceFiction/TimeTravel, History, ScienceFiction/Quantum, Fantasy/Magic
Country: US

Does that help?


I’m going to wing it, as I don’t have the time to setup a full test set just now. Hopefully, I’m not too far off. And this will be almost without explanations. For explanations, see the following query for some explanations related to FLATTEN-ing and GROUP BY stuff: How to view latest linked file in Dataview? - #4 by holroy

TABLE bookCount
FROM "Book Log"
FLATTEN Author as OneAuthor
WHERE Author
GROUP BY OneAuthor
FLATTEN length(rows.OneAuthor) as bookCount

In order for this work, you need to make sure that you author lists are actuallly lists of authors, which depending on whether you’ve got pages for each of the authors, or just the names would look like one of these:

Author:  [ "Neal Stephenson", "Nicole Galland" ]
Author: [ "[[Neal Stephenson]]", "[[Nicole Galland]]" ]
Author: Neal Stephenson 

The two first defines an author list of two authors each, one with no links, and one with links. The last one defines a single author. If you use the format you showed, it’ll be interpreted as one author with a somewhat peculiar name of “Neal Stephenson; Nicole Galland”. In other words, it’ll not add to the count of “Nicole Galland” books you’ve read.

(I presented all of those Author in the same frontmatter, but in the frontmatter you only have one of those definitions. (In the main text you could add several on different lines, and they’ll be added into a list, but in the frontmatter only the last definition counts. ))

Hope I’ve not written the query wrong, but it should show the gist of what you need to do. (And I wrote it as a pure DQL query, so it needs a little translation if you need it to be a dataviewjs query… )

1 Like

Perfect, that works, thank you! I just had to add

SORT bookCount desc
Limit 10

to actually sort by descending frequency (and limit to the 10 most common authors). Awesome :slight_smile:

1 Like

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