Can't wrap my head around GROUP BY - dataview

[paoloap]

I’ve scrolled through virtually all posts related to the GROUP BY function of Dataview, but still can’t wrap my head around it. Surely I must be misinterpreting something basic.

I have a folder called “90 lists” where I collect resources found during my research, for example, “apps”, “communities”, “ppl” etc.
What I’m trying to accomplish is to query “90 lists” to have an overview of the different resources grouped by type (apps, communities, ppl etc.), which is correctly written in YAML format in each note.

Attached is what I’ve tried and the error I get.

To clarify, the folder structure I have in place is
Project/ 90 lists / apps
at the same hierarchy level of apps are communities, people, and others. To say it differently, these are all subfolders of “90 lists”

I believe the folder structure with these sub-levels may be involved in the query not returning the desired result and I’m willing to drop everything into one single folder if this makes things easier

[mnvwvnm]

Many issues with your query:

1. TABLE type, file.title - what’s file.title? There is no implicit field called “file.title”; maybe “file.name”, “file.link”…
2. TABLE type, file.title - after the last table header (in your case the wrong field “file.title”) there is no comma, i.e., you need to remove the comma at the end of the first line.
3. FROM "90 lists"- if the path for your source folder is Project/90 lists, then you need to write the full path to the wanted folder - FROM "Project/90 lists"
4. SORT file.ctime DESC - why you put the sort command before group by ? If you group things after, this sorter is irrelevant.
5. GROUP BY type - When you create a simple table query, you know that the first column is, by default, the file.link. When you create a group, the default first column becomes the group column (in your case the groups of “type”). In consequence, you don’t need “type” as a column header (i.e., in TABLE type this “type” becomes unnecessary). Another thing, when you group things you need to add rows to the other fields… Queries - Dataview
6. query to test:

```dataview
TABLE Type, rows.file.link as "Files"
FROM "Project/90 lists"
GROUP BY type AS Type
SORT Type ASC
```

[paoloap]

thanks for clearing the mess in my mind. This also provides me a foundation for using this function in the future.

I got the exact result I was looking for, and you replied so quickly!
Tomorrow it will be another day of tweaking further my system

Really appreciate your help.

[paoloap]

now, each resource has a “status” property which I’d also like to display

I wrote the following and got the result that you can see attached

table rows.file.link as “Resources”, rows.file.type as “Status”
from “Project/90 lists”
group by type

is this something related to flatten?

[mnvwvnm]

You mentioned “status” but you write rows.file.type!
What’s file.type?
Do you know the difference between implicit fields and created fields?
(see documentation)
Implicit fields use the prefix file., but created fields not! For example, you write group by type, not group by file.type!

[paoloap]

I didn’t know that, the documentation doesn’t seem clear to me.

I changed to:

table rows.file.link as "Resources", status
from "+Project/90 lists"
group by type


![Screen Shot 2022-02-01 at 18.13.09|364x188](upload://uR8L8PKN9WEqqv6PbJI8b4EWq13.png)

but stil not working

[mnvwvnm]

no screen shot but I guess the problem is…
As I said before, if you group things you need to add “rows” to the other fields:

table rows.file.link as "Resources", rows.status as Status
from "+Project/90 lists"
group by type

[paoloap]

now I get it

wonderful, thanks

[paoloap]

Let’s say I want to push this further.

I have a project management table which query the number of open/completed task within each note and return them as “progress”, like this

I’m not sure if it’s possible to reply this in a table with a group by since I understand there are many functions going on simultaneously. Here what I’ve tried

table rows.file.link as “initiatives”, rows.format as “format”, join(choice(file.tasks.completed, “●”, “○”), “”) as progress
from “10 Initiatives”
group by category

which returns the following

any idea?

[mnvwvnm]

I don’t know if I follow you…

1. In your query the first issue is: you need to apply rows to file.tasks.completed, i.e., rows.file.tasks.completed.
2. Why you want to join? I think you can’t get what you want, because it always shows a “,” between your bullets (because multiple tasks in each file and other strange behaviors that I don’t understand), etc. Besides that, a mix of “●” and “○” gives you any ideia without relation to the specific note?
3. Why you don’t use a kind o progress bar like this (per file): Progress bar for incomplete/total tasks?
   (gives you a progress bar similar to this html that you can put directly in one note to test:

<progress value='75' max='100'></progress>

[paoloap]

the formula that I shared count the number of completed ● and uncompleted ○ todos within each note. This is an example of the result. I like this approach because it’s more granular than a progress bar, it tells me how many todos I have and also give me an idea of how long have they been open for - just check line 3 of the screenshot where you can see a ○ among many ●. With this clarification, do you think it’s possible to render this type of progress bar in a table with a group by function?

[paoloap]

I’m also playing around with the other progress bar that you shared - thanks!

I simplified the query to this:

TABLE (length(filter(file.tasks.completed, (t) => t = true)) / length(file.tasks.text)) * 100 AS BB, "<progress value='" + (length(filter(file.tasks.completed, (t) => t = true)) / length(file.tasks.text)) * 100 + "' max='100'></progress>" AS Progress 
FROM "20 Initiatives" 
WHERE file.tasks 

The only issue that I have is with column BB which returns numbers with many decimals. Is it possible to round this number to an integer or 1 decimal?

[mnvwvnm]

But, as I asked before, you want to join all “●” and “○” in the same group? if so,
try this expression (with a trick to remove the auto “,” placed by join())

replace(join(choice(rows.file.tasks.completed, "●", "○"), ""), ",", "") as Progress

[mnvwvnm]

add round(). use this expression:

round((length(filter(file.tasks.completed, (t) => t = true)) / length(file.tasks.text)) * 100) AS BB

[paoloap]

@mnvwvnm

replace(join(choice(rows.file.tasks.completed, "●", "○"), ""), ",", "") as Progress

this worked well, now all the circles got merged in one line.

This is not exactly what I meant to do. Let me clarify, each initiative is assigned a category, so I’m grouping by category to see the different initiatives falling under the same category. Then I’d like to see the progression for each initiative. For example, if a category have 3 initiatives, I’d like to see 3 different lines of progress, whereas now the 3 different lines got merged into one. Do I explain myself?

[paoloap]

beautiful, thanks for your patience with me. I really appreciate your help

[mnvwvnm]

Try another one:

replace(map(rows.file.tasks, (t) => join(choice(t.completed, "●", "○"), ", ")), ",", "") as Progress

[paoloap]

amazing, thank you so much!