CODEWITHSUNDEEP
kdb+k
egor7
egor7

Reputation: 4941

Getting table rows count by using count 1

To get an appropriate table rows count I thought to use a naive approach: use count 1 construct. And it works in a simple case:

q)t:([]sym:`a`a`b`b);
q)select cnt: count 1 by sym from t
sym| cnt
---| ---
a  | 2
b  | 2

But when I added other fields, I've got wrong result:

q)select cnt: count 1, sym by sym from t
sym| cnt sym
---| -------
a  | 1   a a
b  | 1   b b

Why does count 1 work (or just it seems so) in one column case and failed with multiple columns?

Upd: Expected to get something like this

sym| cnt sym
---| -------
a  | 2   a a
b  | 2   b b

Upvotes: 1

Views: 651

Answers (1)

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

I don't think count 1 will produce the result you're looking for, nor even a consistent one.

I think you might want to use count i instead. When selecting by sym you are specifying which column you want to count by.

q)t:([]sym:`a`a`b`b)
q)select cnt:count i,sym by sym from t
sym| cnt sym
---| -------
a  | 2   a a
b  | 2   b b
q).z.K
3.6

A point to note however is that this solution will not work on kdb+ 4.0.

q)t:([]sym:`a`a`b`b)
q)select cnt:count i,sym by sym from t
'dup names for cols/groups sym
  [0]  select cnt:count i,sym by sym from t
                                    ^
q).z.K
4f

Upvotes: 1

Related Questions