Please help with dataview grouping and summation

What I’m trying to do

I have a set of notes logging activities with YAML data detailing begin and end times, and I would like to be able to summarize the notes by summing the durations of each activity grouped by other fields in the notes, specifically by the name and the activity itself.

The data is shown by the following query:

TABLE WITHOUT ID
	substring(split(file.name,"–")[1],0,15) AS Name
	,begin
	,end
	,activity
	,date(end,"yy-MM-dd-HHmm")-date(begin,"yy-MM-dd-HHmm") as Duration
FROM "Logs" AND #type/log/activity
WHERE startswith(begin,"25-02-02")

which will produce the following table:

Name11	begin	end	activity	Duration
Poly Coating	25-02-02-1451	25-02-02-1507	
Woodworking(Act)
16 minutes
Dishes	25-02-02-1606	25-02-02-1630	
Chores(Act)
24 minutes
Poly coating	25-02-02-1828	25-02-02-1839	
Woodworking(Act)
11 minutes
Sunday Supper	25-02-02-1220	25-02-02-1430	
Appointment(Act)
2 hours, 10 minutes
Reading Feeds	25-02-02-0645	25-02-02-0706	
Reading(Act)
Media(Act)
21 minutes
Archiving movie	25-02-02-0715	25-02-02-0719	
Media(Act)
4 minutes
Reading Feeds	25-02-02-1118	25-02-02-1200	
Reading(Act)
Media(Act)
42 minutes
Reading Feeds	25-02-02-1508	25-02-02-1601	
Reading(Act)
Media(Act)
53 minutes
YouTube	25-02-02-1648	25-02-02-1730	
Media(Act)
42 minutes
Reading Feeds	25-02-02-1812	25-02-02-1826	
Reading(Act)
Media(Act)
14 minutes
YouTube	25-02-02-1847	25-02-02-1937	
Media(Act)
50 minutes

Things I have tried

I have tried to use FLATTEN activity, producing the following somewhat better formatted table:

Name15 begin end activity Duration
Poly Coating 25-02-02-1451 25-02-02-1507 Woodworking(Act) 16 minutes
Dishes 25-02-02-1606 25-02-02-1630 Chores(Act) 24 minutes
Poly coating 25-02-02-1828 25-02-02-1839 Woodworking(Act) 11 minutes
Sunday Supper 25-02-02-1220 25-02-02-1430 Appointment(Act) 2 hours, 10 minutes
Reading Feeds 25-02-02-0645 25-02-02-0706 Reading(Act) 21 minutes
Reading Feeds 25-02-02-0645 25-02-02-0706 Media(Act) 21 minutes
Archiving movie 25-02-02-0715 25-02-02-0719 Media(Act) 4 minutes
Reading Feeds 25-02-02-1118 25-02-02-1200 Reading(Act) 42 minutes
Reading Feeds 25-02-02-1118 25-02-02-1200 Media(Act) 42 minutes
Reading Feeds 25-02-02-1508 25-02-02-1601 Reading(Act) 53 minutes
Reading Feeds 25-02-02-1508 25-02-02-1601 Media(Act) 53 minutes
YouTube 25-02-02-1648 25-02-02-1730 Media(Act) 42 minutes
Reading Feeds 25-02-02-1812 25-02-02-1826 Reading(Act) 14 minutes
Reading Feeds 25-02-02-1812 25-02-02-1826 Media(Act) 14 minutes
YouTube 25-02-02-1847 25-02-02-1937 Media(Act) 50 minutes

which has successfully produced a table with one row per activity, but each way I try to GROUP BY either Name or activity I get nulls in all the other columns. And I cannot see how to sum the Duration column at all.

I’m obviously very confused and I have a feeling I’m missing something very basic. Please help.

For some reason I cannot edit the post further…
I have since also tried the following query:

TABLE WITHOUT ID
	rows.activity
	, sum(rows.duration) as sum
FROM "Logs" AND #type/log/activity
WHERE startswith(begin,"25-02-02")
FLATTEN date(end,"yy-MM-dd-HHmm")-date(begin,"yy-MM-dd-HHmm") as duration
GROUP BY activity

which produces a table I cannot paste here (I don’t know why) … it has the correct summations but does not collapse the activities into a single line.

Anybody have a clue to help Please?

Try this variant:

```dataview
TABLE  sum(rows.duration) as sum
FROM "Logs" AND #type/log/activity
WHERE startswith(begin, "25-02-02")
FLATTEN date(end, "yy-MM-dd-HHmm") - date(begin, "yy-MM-dd-HHmm") as duration
GROUP BY activity
```

or possibly use GROUP BY substring(split(file.name, "–")[1], 0, 15) + " - " + activity as Activity as the last line of the query.

Whenever you do GROUP BY all the data grouped by the expression is stored either within the rows list, and the expression itself is used as the main key (and stored as key (or your named variant (like Activity in my query))).

That works splendidly! Thank you so very much!

I have to note that I still don’t grok how FLATTEN & GROUP BY work, but this at least gives me a leg up with the data I already have and I hope to grope my way to some basic fluency now.

Again, thank you!

1 Like