COUNT DISTINCT using OVER clause

sql
#1

Hi there,

I am having trouble calculating the distinct count of IDs over a certain group of rows using the OVER clause but it doesn’t seem to take the DISTINCT keyword.

Code:

COUNT(DISTINCT COALESCE(ABC,0)) OVER (ORDER BY XYZ ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

Error:

Distinct option is invalid with OVER phrase

Thanks in advance

#2

As per section “12.21.5 Window Function Restrictions” in MySQL 8.0 Reference Manual, keyword DISTINCT is not allowed to be used with aggregated window functions. In this scenario, “PARITITION BY ABC” in OVER() and move the COALESCE(ABC,0) in SELECT clause. Then, use this query as sub-query in FROM clause or in WITH (Common Table Expression) clause and in outer query use DISTINCT.
Hope this helps.