How to combine queries

[Supetorus]

I want to create a weekly view of my work logs, with a sum and average. A work log looks like this:

> [!pomo]
> START:: {{date}}T{{time}}
> Intention: 
> Report: 
> END:: {{date}}T{{time}}

And there are any number of these in a daily note. I use this to get the daily total.

TABLE sum(rows.workTime) as "Daily Total"
WHERE file.folder = this.file.folder
WHERE START and END FLATTEN flat(list(START)) as startTime
FLATTEN min(filter(flat(list(END)), (f) => f > startTime)) as endTime
FLATTEN endTime - startTime as workTime
GROUP BY file.link as Day

To get the week total would be as simple as grouping by file.folder instead of file.link.
I’ve also figured out that I can do a query like this to get the number of files in this week’s folder (minus one to account for the weekly summary file)

LIST WITHOUT ID length(rows.file.link)-1
WHERE file.folder = this.file.folder
GROUP BY true

Now my problem is that I have no idea how to combine these two queries to get the average by dividing the total number of hours divided by the number of files to get a daily average.

[holroy]

Do you want to combine the daily totals list with a sum at the end for the weekly average? In this case you need to do something like shown in the post below:

Automating Timesheet Help - Query embedded fields and sum hours across all files in current folder

I kind of triggered myself with these sum across the board, so here is a rather intense query doing three different sums in two tables. Before I present the query, and an output example, lets re-iterate on the base for the query, and which totals we’re going to build. Base query requirements (with three different sum variants) The base data is a standard DQL query holding the columns: project, date, tasks, hours. In all the sums below, we’re aiming to sum the hours in various ways, and the tab…

If you want to make another table just showing the weekly average, this is the way to transform your queries. First you need to move any calculated fields from the TABLE definition into a FLATTEN statement after the GROUP BY.

TABLE sum(rows.workTime) as dailyTotal
WHERE file.folder = this.file.folder
GROUP BY file.link as Day

Changes into

TABLE <to be decided>
WHERE file.folder = this.file.folder (+ plus the other stuff)
GROUP BY file.link as Day
FLATTEN sum(rows.workTime) as dailyTotal

And now we can do another GROUP BY and introduce the new values we want to display.

TABLE WITHOUT ID average(rows.dailyTotal) as "Weekly average"
WHERE file.folder = this.file.folder (+ plus the other stuff)
GROUP BY file.link as Day
FLATTEN sum(rows.workTime) as dailyTotal
GROUP BY true

Hopefully that make sense, and please note that you do need to keep the other stuff in between the first WHERE and GROUP BY as you had originally. I’ve left it out of the examples to clarify which steps needs to be taken to transform your query.