Analytic function to count each customer with many accounts only once

Question

I'm having a slow day and for some reason am blanking on how to do this. Please help me. I have an Analytic function to count each customer with many accounts only once. Want a total of each distinct customer. I have to list the customers with their accounts. Many accounts for each customer

I'm using functions like this. COUNT(1) OVER(partition by cust.SK_CUST_RM_ID) total_cnt1

I'm looking for this output

cust account  distinc_cust
 1     a           3
 1     b           3
 2     a           3
 2     b           3
 2     c           3
 3     a           3

Answer 1

I don't think Netezza supports count(distinct) as an analytic function. So, you can use this trick:

select t.*,
       max(dr) over () as distinct_cust
from (select t.*, dense_rank() over (order by cust) as dr
      from t
     ) t;

As an analytic functions, you would just use:

select t.*, count(distinct cust) over ()
from t;