KDB count unique combination of columns

Question

assuming I have a table like

tbl:flip `id`evt!(1 1 1 2 2 2 2 2 3 3; `a`b`c`a`b`b`b`c`a`c)

How can I efficiently count the occurrences of evt but only once per id

So, the result should look like this (or any other format that maps evt to the unique count)

res:flip `evt`ct !(`a`b`c; 3 2 3)

Answer 1

One way to do it would be something like this:

q)select count distinct id by evt from tbl
evt| id
---| --
a  | 3
b  | 2
c  | 3

If you want it unkeyed like in your sample, you can add 0! to the start