CODEWITHSUNDEEP
kdb+
Aarif1430
Aarif1430

Reputation: 155

Selecting count of occurences of values in kdb

How to count occurences of distinct values from one column in another column in kdb. The idea is to return the count of values in another column.

The table looks like Col1 : x,y,z and Col2: x,x,l The idea is to find count of occurences of x,y,z from col1 in col2, which in above case is 2,0,0

Upvotes: 1

Views: 2488

Answers (2)

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

Desired value can be found as a map of Col2 occurrences. Which is later looked up by values from Col1

t: ([] Col1:`x`y`z; Col2:`x`x`l);
update Col1Col2Count: 0^(count each group Col2) Col1 from t

Upvotes: 0

terrylynch
terrylynch

Reputation: 13572

You could try this:

tab:([]col1:`x`y`z;col2:`x`x`w)

q)exec([]distinct col1)!0^([]count each group col2)distinct col1 from tab
col1| col2
----| ----
x   | 2
y   | 0
z   | 0

Upvotes: 2

Related Questions