How to "distinct" count in a window function in Postgresl?

Question

I've a simple table -

--------------------------------------------------
| srcip | dstip | dstport            
--------------------------------------------------
| X     | A     | 80
--------------------------------------------------
| X     | A     | 443
--------------------------------------------------
| X     | B     | 8080
--------------------------------------------------

I want output like this-

--------------------------------------------------
| srcip | dstip | count            
--------------------------------------------------
| X     | A     | 2
--------------------------------------------------
| X     | B     | 1
--------------------------------------------------

I am trying to use COUNT(distinct dstport) OVER(PARTITION BY dstip,dstport) as count in window function but getting error WINDOW definition is not supported

Answer 1

First, as you've written the question, the value is always "1" (or perhaps NULL). The code is counting dstport and you are partitioning by the value. So, there can be only one.

You can do this with two levels of window functions. Here is one way:

select t.*,
       sum( (seqnum = 1)::int ) as count_distinct
from (select . . . ,
             row_number() over (partition by dstip order by dstport) as seqnum
      from . . .
     ) t

Answer 2

the simplest way is to use count with two column is as below:

SELECT srcip , dstip , COUNT(dstip) FROM tbl GROUP BY srcip , dstip