org mode sum time from remote table with org-lookup-all

Question

I have two tables

#+NAME: tasks
| name   | spent  |
|--------+--------|
| task 1 |        |
| task 2 |        |
| task 3 |        |
| task 4 |        |
| task 5 |        |

#+NAME: works
| date             | task   |     time |
|------------------+--------+----------|
| <2020-10-17 Sat> | task 1 |     0:05 |
| <2020-10-18 Sun> | task 1 |     0:10 |
| <2020-10-19 Mon> | task 2 |     1:00 |
| <2020-10-20 Tue> | task 3 |     1:25 |
| <2020-10-21 Wed> | task 4 |     0:10 |
| <2020-10-22 Thu> | task 4 |     2:30 |

How to calculate total time spent to task in table tasks column spent using org-lookup-all?

Answer 1

The code works for me:

(org-table-time-seconds-to-string
 (apply '+
        (mapcar 'string-to-number
                (mapcar 'org-table-time-string-to-seconds
                        (org-lookup-all
                         $1
                         '(remote(works,@2$2..@>$2))
                         '(remote(works,@2$3..@>$3))))))
 'hh:mm)

org-lookup-all returns something like ("0:05" "0:10").

---

#+NAME: works
| date             | task   |     time |
|------------------+--------+----------|
| <2020-10-17 Sat> | task 1 |     0:05 |
| <2020-10-18 Sun> | task 1 |     0:10 |
| <2020-10-19 Mon> | task 2 |     1:00 |
| <2020-10-20 Tue> | task 3 |     1:25 |
| <2020-10-21 Wed> | task 4 |     0:10 |
| <2020-10-22 Thu> | task 4 |     2:30 |

#+NAME: tasks
| name   | spent |
|--------+-------|
| task 1 | 00:15 |
| task 2 | 01:00 |
| task 3 | 01:25 |
| task 4 | 02:40 |
| task 5 | 00:00 |
#+TBLFM: $2='(org-table-time-seconds-to-string (apply '+ (mapcar 'string-to-number (mapcar 'org-table-time-string-to-seconds (org-lookup-all $1 '(remote(works,@2$2..@>$2)) '(remote(works,@2$3..@>$3)))))) 'hh:mm)

I learnt how to use the function via https://orgmode.org/worg/org-tutorials/org-lookups.html, the webpage contains some examples.

Answer 2

In Calc (the Emacs embedded calculator) there is a format for hours-minutes-seconds that you may leverage. For instance, 1 hour, 25 minutes, 17 seconds is written 1@ 25' 17". Here is a variation of xuchunyang's answer with this format:

The input table:

#+NAME: works
| date             | task   | time      |
|------------------+--------+-----------|
| <2020-10-17 Sat> | task 1 | 0@ 05' 0" |
| <2020-10-18 Sun> | task 1 | 0@ 10' 0" |
| <2020-10-19 Mon> | task 2 | 1@ 00' 0" |
| <2020-10-20 Tue> | task 3 | 1@ 25' 0" |
| <2020-10-21 Wed> | task 4 | 0@ 10' 0" |
| <2020-10-22 Thu> | task 4 | 2@ 30' 0" |

The sums:

#+NAME: tasks
| name   | spent     |
|--------+-----------|
| task 1 | 0@ 15' 0" |
| task 2 | 1@ 0' 0"  |
| task 3 | 1@ 25' 0" |
| task 4 | 2@ 40' 0" |
| task 5 | 0         |
#+TBLFM: $2='(math-format-value (calcFunc-vsum (cons 'vec (mapcar #'math-read-expr (org-lookup-all $1 '(remote(works,@2$2..@>$2)) '(remote(works,@2$3..@>$3)) )))))

Note that Org Mode tables understand this @ ' " format. Therefore you may use it in other ways.

Answer 3

Alternately, you may want to look at the OrgAggregate package available on Melpa. It does all kinds of aggregations. In your example with the @ ' " format, it will use the task column as a key, and sum up the time column values. You don't have to create the receiving table, OrgAggregate takes care of it.

#+BEGIN: aggregate :table "works" :cols "task vsum(time)"
| task   | vsum(time) |
|--------+------------|
| task 1 | 0@ 15' 0"  |
| task 2 | 1@  0' 0"  |
| task 3 | 1@ 25' 0"  |
| task 4 | 2@ 40' 0"  |
#+END:

Documentation here: https://github.com/tbanel/orgaggregate