Getting table rows count by using count 1

Question

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

Can you share your expected output from this query? Also, is it `count i` you're trying to use or `count l`? — Cathal O'Neill, Feb 01 '21 at 11:47
`count 1` - like in Oracle, though in Oracle this simply references to the first column. — egor7, Feb 01 '21 at 13:36

Cathal O'Neill · Answer 1 · 2021-02-01T17:32:49.487

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

Getting table rows count by using count 1

1 Answers1