Group results by value and calculate the sum on each group

Questions & Help Queries
1

Hi everyone,
I have properties project:: projectName and duration:: 2 per day and I would like to have for each week a list of the project and the total duration spent on each of them.

Doing a normal query such as:

#+BEGIN_QUERY
{
:title "Weekly timetracker"
  :query [
    :find (pull ?b [*])
:in $ ?start ?end
    :where
    [?b :block/page ?p]
    [?b :block/refs ?r]
    [?r :block/name "timetracker"]
    [?p :block/journal? true]
    [?p :block/journal-day ?d]
   [?b :block/properties ?props]
  [(get ?props :project) ?project]
  [(get ?props :duration) ?duration]
(between ?b ?start ?end )
  ]
:inputs [ 20241020 20241025 ]
}
#+END_QUERY

will just list all the projects and the times.
e.g.

|page|project|duration|
|-|-|-|
|Fr, 2024/10/25|projA|4|
|Fr, 2024/10/25|projB|2|
|Fr, 2024/10/26|projA|4|

I would like to end up with a table that has 2 columns project and duration where each project name would appear only once and the duration is the sum of the duration for a particular project.

|project|duration|
|-|-|
|projA|6|
|projB|2|

Does someone have a nice approach for it?

2

Welcome. Try something like this:

#+BEGIN_QUERY
{:title "timetracker"
 :inputs [ 20241103 20241108 ]
 :query [
   :find (pull ?project [*]) (sum ?duration)
   :keys project duration
   :in $ ?start ?end
   :where
     [?r :block/name "timetracker"]
     [?b :block/refs ?r]
     (between ?b ?start ?end)
     [?b :block/page ?p]
     [?b :block/properties ?props]
     [(get ?props :duration) ?duration]
     [(get ?props :project) ?project-val]
     [?project :block/original-name ?project-name]
     (or
       [(= ?project-val ?project-name)]
       [(contains? ?project-val ?project-name)]
     )
 ]
 :result-transform (fn [result]
   (map (fn [r]
     (update (:project r) :block/properties (fn [p]
       (assoc p "duration" (:duration r) )
     ) )
   ) result)
 )
}
#+END_QUERY
  • The result should be later adjusted to show a table with the desired columns.
  • In the last or-clause you may want to remove one of the lines, depending on whether the value of the project:: property is either:
    • plain text
    • a reference
3

thank you for the quick reply. if the content of the project is a string this does not seems to work, however this works with if the projects are references.

I still encounter a major bug: if 2 occurences of the same project with the same duration appears, then only one is summed leading to the wrong sum. If the duration are different then the sum is calculated properly. Does someone know how we could fix the query proposed by @mentaloid to end up with the proper sum all the time?

EDIT/NOTE: this also does not seems to work with fractional number. for one project I have 4 entries, 8,7,1,8 he sums up to 16 instead of 24. If I change one of the 8 to 8.0 he sums to 88.07 ??!!??

4

I found the solution already proposed by @mentaloid in another post: Add decimal values
we need to add :with ?b in order to fix the proper counting.
we need to multiply by 1 to force the duration being interpreted as a number (* 1 ?sdur) ?duration)

#+BEGIN_QUERY
{:title "timetracker"
 :inputs [ 20241103 20241108 ]
 :query [
   :find (pull ?project [*]) (sum ?duration)
   :with ?b
   :keys project duration
   :in $ ?start ?end
   :where
     [?r :block/name "timetracker"]
     [?b :block/refs ?r]
     (between ?b ?start ?end)
     [?b :block/page ?p]
     [?b :block/properties ?props]
     [(get ?props :duration) ?sdur]
     [(* 1 ?sdur) ?duration]
     [(get ?props :project) ?project-val]
     [?project :block/original-name ?project-name]
     (or
       [(= ?project-val ?project-name)]
       [(contains? ?project-val ?project-name)]
     )
 ]
 :result-transform (fn [result]
   (map (fn [r]
     (update (:project r) :block/properties (fn [p]
       (assoc p "duration" (:duration r) )
     ) )
   ) result)
 )
}
#+END_QUERY
1 Like