Hi Stefan On Mon, Feb 3, 2014 at 4:25 PM, Stefan Huchler <stefan.huch...@mail.de> wrote: > I get with the 1. COLUMNS line something like that: > > Tea Shop 1 | 5.0 | ... > Tea Shop 2 | 7.0 | ... > > I want another output that looks like that: > > Blend 1 | 6.0 | ... > Blend 2 | 3.0 | ...
My suggestion for these several GROUP BY as in SQL on the same data is to use Org babel with R or TBLFM. I am not a user of the R programming language and therefore can not provide a working example but it would be one of the simpler solutions. Put all data into one Org table just like "#+NAME: data" below and for each result table use an Org babel source block with R that reads from this table. Some R example of GROUP BY as in SQL: http://stats.stackexchange.com/questions/8225/how-to-summarize-data-by-group-in-r With TBLFM the only thing that comes to my mind is org-lookup-all. The disadvantage of this solution is that you have to enumerate all groups manually in the result tables: #+NAME: data | Purchase | Product | Shop | Rating | |----------+---------+------+--------| | a | p1 | s1 | 1 | | b | p1 | s2 | 4 | | c | p2 | s1 | 2 | | d | p3 | s2 | 8 | Product rating and ranking by average purchase: | Product | Rating | Ranking | |---------+--------+---------| | p1 | 2.5 | 2 | | p2 | 2.0 | 3 | | p3 | 8.0 | 1 | #+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data, @I$2..@>$2)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2) nil '<))); N Shop rating and ranking by average purchase: | Shop | Rating | Ranking | |------+--------+---------| | s1 | 1.5 | 2 | | s2 | 6.0 | 1 | #+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data, @I$3..@>$3)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2) nil '<))); N I added the above TBLFM solution as an ERT that can also be found via Worg: On the page with the Worg tutorials in the spreadsheet section follow the link to the "Emacs Regression Test (ERT) for Org tables" with example use cases for TBLFM: http://orgmode.org/worg/org-tutorials/index.html#Spreadsheet In the ERT it is test-org-table/org-lookup-all. Michael
