sql - Netezza conditionally count one record per ID -


i'm new netezza , need count number of ids have non-zero key. key transaction type, , want return count of ids have had transaction. want count of uids have non-zero key.

my data:

src uid key ...   118 3   ...   517 0   ...   517 1   ...   517 4   ... b   623 4   ... c   972 0   ... c   972 0   ... 

what want return:

source  uids    uids_w_trans       2       2 b       1       1 c       1       0 

here's code:

select     src source,     count(distinct(uid)) uids,     sum(case when key = 0 0 else 1) uids_w_trans      database  group     uid,     source 

what i'm getting

source  uids    uids_w_trans       2       3 b       1       1 c       1       0 

you can see query counting every non-zero key. i've tried number of variations on above query, nothing has gotten me closer. how can count 1 non-zero key per uid?

i think looking conditional count(distinct):

select src source,        count(distinct(uid)) uids,        count(distinct case when key <> 0 uid end) uids_w_trans database group source 

Comments

Popular posts from this blog

'hasOwnProperty' in javascript -

python - ValueError: No axis named 1 for object type <class 'pandas.core.series.Series'> -

java - How to provide dependency injections in Eclipse RCP 3.x? -