Q:

select count distinct column in table new filed

I have a query result, and there is 3 different p_id (3160, 3301, 41)

I want to select count(distinct p_id) and as shown p_id_count_true (see image), p_id_count is incorrect,
how I can write subquery and get p_id_count_true

A:

You appear to want:
select . . .,
       count(distinct pid) over () as num_pids
from t

Alas, Postgres does not support distinct in window functions.  One workaround is:
select . . .,
       sum( (seqnum_pid = 1)::int ) over () as num_pids
from (select t.*, row_number() over (partition by pid order by pid) as seqnum_pid
      from t
     ) t

Some people prefer the dense_rank() method instead:
select . . .,
       max( seqnum_pid ) over () as num_pids
from (select t.*, dense_rank() over (order by pid) as seqnum_pid
      from t
     ) t

