Obsidian Dataview: how to do a count with group by and a total

Help
1

New to Obsidian and dataview.

What I’m trying to do

I have a series of notes with different suffixes.
There are mainly three types of file names: ending with " PA" (ie, space then PA, without the quotes), ending with " TS", and ending with " FB".

For example: “My first note PA”, or “My second note FB”.

I need to display a summary table with the different suffixes and their counts, like:
PA 5
TS 2
FB 3
Total 10

Things I have tried

I have tried this:

TABLE "PA" as suffix, length(rows) as number
WHERE contains(file.name, " PA")
group by suffix

Problems:
It only shows one type. I don’t know how to show the rest, or the total. Plus it shows an additional column.

2

Like this?

```dataview
TABLE length(rows) AS "file count"
GROUP BY choice(
		endswith(file.name, " PA"), "PA", choice(
			endswith(file.name, " TS"), "TS", choice(
				endswith(file.name, " FB"), "FB", "-"
			)
		)
	) AS suffix
WHERE suffix = "PA" OR suffix = "TS" OR suffix = "FB"
```

And then for the total:

```dataview
LIST without ID "Total " + length(rows)
WHERE endswith(file.name, " PA") OR endswith(file.name, " TS") OR endswith(file.name, " FB")
GROUP BY ""
```

They look like this:

result
result1686×557 23.7 KB

Your WHERE statement was telling Dataview to look at only the PA files; that’s why only those were showing up. This new code includes all three types of files.

And it looks only at the end of the filename (endswith) so that something like “JILL AND PAM.md” won’t accidentally get included.

I did the total count separately because I think you might need dataviewJS to put them in one table, or a br in the TABLE statement. Two queries seemed better than one in this case.

(edit to add screenshot)

3

Hi @Pratik_b3 , welcome to the Obsidian community! Fellow user here.

Thanks for your question. I played around with this a bit, and found something that worked for me:

I created a few files that ended in specific suffixes in my “Scratch” folder:

image
image273×224 3.88 KB

Then I used the following query:

```dataview
TABLE length(rows) as Count
FROM "Scratch"
FLATTEN regexreplace(file.name, "^.* (..)$", "$1") as Suffix
WHERE Suffix = "PA"
   OR Suffix = "TS"
   OR Suffix = "FB"
GROUP BY Suffix
```

This gave me the following report:

image
image735×233 6.1 KB

The FLATTEN line might be new to you. FLATTEN creates a new column in each row containing the result of the expression; in this case, pulling out the last two letters of the file name. Then the GROUP BY command rolls the rows up by suffix, and the length() function counts them.

Maybe this will be a useful starting point for you. Hope it helps!

Craig

4

Thank you, both of you. Very much appreciated.

5

Do you have any other files, except those with the suffixes? Can you somehow differentiate these files using some other means, or do we need to actually match against the extracted suffix?