It’s not trivial to both get the overall total, and also the total of a given case within the same table. I tend sometimes to use dataviewjs to do this, but there are at least two other options, which I’ll explore below:
Using filter and GROUP BY true
You’ve presented queries which are able to calculate each of the totals, and you could’ve expanded the one getting the overall total, to do a filter counting only the valid test cases within the resulting rows.
However when you’re using containsword() to check you need to be aware that this function works different when presented with a list versus a string. See its documentation.
One query to do this using that function would be to do:
```dataview
TABLE WITHOUT ID
totalCount, posCount, negCount, rows.testCase
FROM "ForumStuff/f69/f69984"
WHERE subject
FLATTEN any(containsword(subject, "first")) as testCase
GROUP BY true
FLATTEN length(rows) as totalCount
FLATTEN length(filter(rows.testCase, (r) => r)) as posCount
FLATTEN length(filter(rows.testCase, (r) => !r)) as negCount
```
Note that my queries use the FROM "ForumStuff/..." and tests the subject for first, instead of your examples, so you need to change those to match your case.
Another option with the same kind of result would be to use contains():
TABLE WITHOUT ID
totalCount, posCount, negCount
FROM "ForumStuff/f69/f69984"
WHERE subject
GROUP BY true
FLATTEN length(rows) as totalCount
FLATTEN length(filter(rows.subject, (r) => contains(r, "first") )) as posCount
FLATTEN length(filter(rows.subject, (r) => !contains(r, "first") )) as negCount
This latter one would possibly be easier to use if you wanted to test for multiple cases, but it kind of depends on preferences.
Using dual GROUP BY statements
Yet another variant would be to use dual GROUP BY statements. By this I mean that we first do a query listing all rows to be included in the statistics, preferably with extra variables (by using FLATTEN) denoting the various test cases.
Ignoring the first line, which is heavily impacted by later statements, we could imagine this to look like:
TABLE count, round(count / total, 2) as Ratio
FROM "ForumStuff/f69/f69984"
WHERE subject
FLATTEN any(containsword(subject, "first")) as testCase
Same caveats as before regarding the folder, subject and text to match for.
On this overall query we do the first group by to gives us the total number of rows in our test set, and then we proceed to split this set into groups for each test case.
GROUP BY true
FLATTEN length(rows) as totalCount
FLATTEN rows.testCase as case
Finally we group by each test case, and do some final calculation and beautifications on our variables, to allow for the first line (with TABLE) to be manageable and readable:
GROUP BY case
FLATTEN length(rows) as count
FLATTEN rows.totalCount[0] as total
Gather all of these pieces back into one query, and you should be good to go with one row for each of the test cases. An untested version corrected back to your environment should look something like:
```dataview
TABLE count, round(count / total, 2) as Ratio
FROM "Folder"
WHERE reviewtags
FLATTEN any(containsword(reviewtags, "early")) as testCase
GROUP BY true
FLATTEN length(rows) as totalCount
FLATTEN rows.testCase as case
GROUP BY case
FLATTEN length(rows) as count
FLATTEN rows.totalCount[0] as total
```
