Message-ID: <04759d61-3782-2787-5d66-3bc93bfc4230@gmail.com>
Date: Fri, 9 Dec 2022 12:38:07 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Change detection
Content-Language: en-CA
To: pgsql-sql@lists.postgresql.org
References: 
 <CA+i5JwZ+XmOF6P4Lo=UTVUJBSQN4EyQL-fe7t4XTLPB9bwq9aQ@mail.gmail.com>
 <CAB-JLwZCEZNs_2jpd1UxRhC1=L3X0VeubokhQ5QBvhz3RckcZA@mail.gmail.com>
 <CA+i5Jwbi6GVDT5d_HUTpQ+RMupjCm4hR4RkojLVgZPdv96LsmQ@mail.gmail.com>
 <CAB-JLwa2voYyJUmzfanAJzvi0oW4SqEeQd5K2iO8bA_N8PE+nA@mail.gmail.com>
 <CA+i5JwZccb-d8qh+5_R6LfXzT2qggKbrTosp5veqqqfNjosioA@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CA+i5JwZccb-d8qh+5_R6LfXzT2qggKbrTosp5veqqqfNjosioA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/04759d61-3782-2787-5d66-3bc93bfc4230%40gmail.com>
Precedence: bulk

On 12/9/22 12:32, Shaozhong SHI wrote:
> That works well.
>
> I just wonder whether we can tell Tom or Tim has worked in more than 1 
> department.  Apparently, PostgreSQL does not allow count(distinct 
> department) when window function is used.
>
> Given this data set, can we do something like count(distinct) to 
> provide an answer to how many different department someone has worked in?
>
> Regards,
>
> David
Use the working query in a CTE and simply do the count(distinct 
department) on that, else there's no good place to put the number of 
departments per person.

Pretty sure you've been asked before but Please don't top post.