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

Help
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