Sum of hours in Dataview

[Mtin]

I’ve read a lot of previous topics about sums in Dataview but I can’t wrap my head around it. All the topics I’ve read are convoluted with other formatting, formulas etc. I’m trying to just understand the fundamental and have a very basic sum. Help is greatly appreciated.

What I’m trying to do

I’m just trying to sum up the hours in the table, maybe grouped by the month.

Things I have tried

I’ve tried the queries below. The first works fine, the second doesn’t as you can see in the screenshot above.

TABLE WITHOUT ID
	Day,
	Time,
	Task
From "Z - Playground/Note"

TABLE WITHOUT ID
	Day.month AS Month,
	sum(map(rows, (r) => default(r.Time, 0))) AS "Sum"
FROM "Z - Playground/Note"
GROUP BY Day.month

[holroy]

Try the following queries, and see if they do something like what you’re looking for:

## Base query
```dataview
TABLE WITHOUT ID item.Day, item.children.text, cTime, cTask
FROM "Z - Playground/Note"
FLATTEN file.lists as item
FLATTEN filter(item.children, (c) => c.Task)[0].Task as cTask
FLATTEN filter(item.children, (c) => c.Time)[0].Time as cTime
WHERE item.Day
```

## Grouped by month, and summed
```dataview
TABLE sum(rows.cTime) as "Time", rows.cTask as "Tasks"
FROM "Z - Playground/Note"
FLATTEN file.lists as item
FLATTEN filter(item.children, (c) => c.Time)[0].Time as cTime
FLATTEN filter(item.children, (c) => c.Task)[0].Task as cTask
WHERE item.Day
GROUP BY dateformat(item.Day, "yyyy-MM") as Month
```

The trick to both of these queries is that we’re picking out only the list items actually having a Day inline field, and then we look at the children of that item to lift the Time and Task from it. In the second query we then group on the Day field, and since we group then we need to do the sum on the rows.cTime field.

[Mtin]

Thank you, it does work!

I still don’t understand how it works. Maybe I’m not smart enough, maybe my missing computer science education shows, who knows. Maybe I’ll figure it out at some point if I have the time/energy.

With that said, is there anything I could do differently with data entry or anything else that would make it easier? To me it looks like the whole query is very sophisticated for such a seemingly easy task.
Or is it just the way Dataview, in combination with Obsidian, is?

[holroy]

Stuff like this takes some getting used to, but you’ll hopefully get the hang of it after a little while. And this is indeed a somewhat complex query.

The complicating factor for the previous query is the use of both the list item and its children to get out information. If we collate all the information into the same list item, the query is simpler. Try the following in a note of its own:

## Two alternate syntaxes

- (date:: 2023-10-30): (desc:: task 1) , (time:: 40min)
- (date:: 2023-11-03): (desc:: task 2), (time:: 120min)
- [date:: 2023-11-09]: [desc:: task 3], [time:: 90min]

I'm using `date` not `day`, just not to confuse my other test queries in the same file. :-) And `desc` instead of `task`, as both `task` and `text` have special meaning within lists...

```dataview
TABLE sum(rows.item.time), rows.item.desc
WHERE file = this.file
FLATTEN file.lists as item
WHERE item.date
GROUP BY dateformat(item.date, "yyyy-MM") as Month
```

This yields the result of:

Notice the difference in appearance whether you’re using ( ... ) or [ ... ], and also know that the latter variant can be fully targeted using CSS for visual styling if that is something one desires.