Apply function on each column of table with group-by variable

Community Support
1



Hi all,


I am looking to apply a function on each column of a table grouped by one of the columns. For eg., given a table, t –


t:( id:aabbccaa; a:1 2 3 1000; b: 10 20 30 1000; c: 100 200 300 1000)


q)t

id a b c

-----------------

aa 1 10 100

bb 2 20 200

cc 3 30 300

aa 1000 1000 1000



I would like to get –


select sum a, sum b, sum c by id from t


id| a b c

--| --------------
aa| 1001 1010 1100
bb| 2 20 200
cc| 3 30 300



However, my table has ~ 100 columns, and it is cumbersome to write sum a, sum b, sum c … for each 100 variable. So, I need a parameterized query along the lines of …


select { sum x} each cols t by id from t / This won’t work, but it gives the general idea


such that I can get the sum of each column without having to explicitly specify the column names.


Thanks in advance,


- Raj.


2

Please use functional select.

http://code.kx.com/wiki/JB:QforMortals2/queries_q_sql#Functional_select

3

Hi Raj,

Here is an example of how you might write a functional select for your case;

q)t:( id:aabbccaa; a:1 2 3 1000; b: 10 20 30 1000; c: 100 200 300 1000)
q)t
id a??? b??? c

aa 1??? 10?? 100
bb 2??? 20?? 200
cc 3??? 30?? 300
aa 1000 1000 1000
q)?[t;();id!id:enlist `id;c!sum,'c:cols[t] except id]

id a??? b??? c
aa 1001 1010 1100
bb 2??? 20?? 200
cc 3??? 30?? 300

Regards,

Paul

4

Search for functional select on code.kx.com

5

{sum x}@‘’`id xgroup t