I have the following org table where I actively record columns 1,2,3,4 and the remaining columns are calculated from the first four. Using this I calculate the aggregate from the last three columns using orgaggregate
link.
MWE
#+TITLE: Workout
#+NAME: helpers
#+begin_src emacs-lisp
(defun k/get-week (org-date-time)
(set 'year (nth 5 (org-parse-time-string org-date-time)))
(set 'month (nth 4 (org-parse-time-string org-date-time)))
(set 'day (nth 3 (org-parse-time-string org-date-time)))
(car
(calendar-iso-from-absolute
(calendar-absolute-from-gregorian
(list month day year)))))
#+end_src
* Horizontal Push
#+NAME: hpush
| date | progression | sets-reps | comment | year | week-number | total-volume |
|------------------+-------------+-----------+---------------------------------------+------+-------------+--------------|
| <2021-11-26 Fri> | push up | 5 5 5 5 5 | Hard on wrists | 2021 | 47 | 25 |
| <2021-11-30 Tue> | push up | 8 8 8 8 8 | Parallettes | 2021 | 48 | 40 |
| <2021-12-03 Fri> | push up | 8 8 8 8 8 | parallettes inclined | 2021 | 48 | 40 |
| <2021-12-06 Mon> | push up | 3 3 3 3 4 | parallettes parallel | 2021 | 49 | 16 |
| <2022-01-03 Mon> | push up | 5 5 5 | parallettes parallel right difficulty | 2022 | 1 | 15 |
#+TBLFM: $7='(reduce '+ (mapcar 'string-to-number (split-string $3 " ")))
#+TBLFM: $5='(nth 5 (org-parse-time-string $1))
#+TBLFM: $6='(k/get-week $1)
#+BEGIN: aggregate :table "hpush" :cols "'year' 'week-number' sum('total-volume')"
| 'year' | 'week-number' | sum('total-volume') |
|--------+---------------+---------------------|
| 2021 | 47 | 25 |
| 2021 | 48 | 80 |
| 2021 | 49 | 16 |
| 2022 | 1 | 15 |
#+END:
# Local Variables:
# org-confirm-babel-evaluate: nil
# eval: (progn (org-babel-goto-named-src-block "helpers") (org-babel-execute-src-block) (outline-hide-sublevels 1))
# End:
I would like to record only columns date, progression, sets-reps, comment
in the first table and not calculate the remaining columns in the first table but create a second table (using the first table's 4 columns) which will have year, week-number, sum(total-volume)
.
With SQL, I'd roughly be doing this
select to_year(date), get_week_number(date), sum(sets-reps)
from table
group by to_year(date), get_week_number(date);
I'm trying to get weekly volume of my reps.