CODEWITHSUNDEEP
sqlwindow-functions
Garrett
Garrett

Reputation: 35

Excluding fields while using Over() function in SQL

I am using a count(field_name) Over(partition by field_name) function in sql and wanted to only show the values greater than 1. I found that I cannot use an aggregate function in the where or having clause and was hoping there was another way, short of writing a function as I do not have write privileges.

Any help would be greatly appreciated. Thanks

Upvotes: 1

Views: 71

Answers (1)

Mureinik
Mureinik

Reputation: 312404

COUNT excludes nulls, so instead of counting the column itself, you can add some logic there by introducing a case expression:

COUNT(CASE WHEN field_name > 1 THEN field_name ELSE NULL END) OVER (PARTITION BY field_name) f

EDIT:
I seem to have misunderstood the original question. If you want to filter out the results of the count function, you'll need a subquery:

SELECT office, cnt
FROM   (SELECT office, COUNT(office) OVER(PARTITION BY office) cnt
        FROM   my_table)
WHERE  cnt > 1

Upvotes: 1

Related Questions