COUNT as a Window function throws error msg 4403 on SQL Server-CodePudding

This is my code:

WITH ActivityCTE AS
(
    SELECT DISTINCT 
        id,
        COUNT(*) OVER (PARTITION BY id) ctn
    FROM 
        dailyActivity_merged$
)
DELETE ActivityCTE
WHERE ctn <> 31

Could someone explain to me why COUNT() used in a window function throws an error message 4403?

Cannot update the view or function 'ActivityCTE' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

enter image description here

I'm trying to delete some records from my table that don't have 31 entries using a CTE.

CodePudding user response：

Count(*) is not the root of the error.

The error message details ...DISTINCT...

WITH ActivityCTE AS(
    SELECT *
          ,COUNT(*) OVER( PARTITION BY id) ctn
    FROM dailyActivity_merged$
)
DELETE FROM ActivityCTE
WHERE ctn <> 31

CodePudding user response：

FWIW, I'd write it with a GROUP BY/HAVING, rather than a window function, and delete from the actual table, like this:

WITH ActivityCTE AS(
    SELECT id
    FROM dailyActivity_merged$
    GROUP BY id
    HAVING count(*) <> 31
)
DELETE dam
FROM dailyActivity_merged$ dam
INNER JOIN ActivityCTE cte on cte.id = dam.id