kdb+: group by and sum over multiple columns

Question

Consider the following data:

table:

time          colA    colB     colC
-----------------------------------
11:30:04.194  31      250      a
11:30:04.441  31      280      a
11:30:14.761  31.6    100      a
11:30:21.324  34      100      a
11:30:38.991  32      100      b
11:31:20.968  32      100      b
11:31:56.922  32.2    1000     b
11:31:57.035  32.6    5000     c
11:32:05.810  33      100      c
11:32:05.810  33      100      a
11:32:14.461  32      300      b

Now how can I sum colB whenever colC is the same, without losing the time order.

So the output would be:

first time    avgA    sumB     colC
-----------------------------------
11:30:04.194  31.2    730      a
11:30:38.991  32.07   1200     b
11:31:57.035  32.8    5100     c
11:32:05.810  33      100      a
11:32:14.461  32      300      b

What I have so far:

select by time from (select first time, avg colA, sum colB by colC, time from table)

But the output is not grouped by colC. How should the query look like?

WooiKent Lee · Accepted Answer · 2015-04-29 12:45:08Z

4

How about this?

get select first time, avg colA, sum colB, first colC by sums colC<>prev colC from table

answered Apr 29, 2015 at 12:45

WooiKent Lee

1,3117 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nyi · Accepted Answer · 2018-06-19 22:07:34Z

1

A slightly different way to achieve this using differ :

value select  first time, avg colA, sum colB , first colC by g:(sums differ colC) from table

answered Jun 19, 2018 at 22:07

nyi

3,2494 gold badges25 silver badges47 bronze badges

Collectives™ on Stack Overflow

kdb+: group by and sum over multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related