CODEWITHSUNDEEP
sqlsql-server
korrowan
korrowan

Reputation: 583

select with multiple where and count

The following query returns what is needed but I need to modify it so I can only select the records from documents in which the number of DocID is greater than 3 along with what is in the where clause. When attempting to do so I always get the error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

How would I write the query to satisfy the count of DocID being greater than 3? DocID is the PK of the documents table.

use newCityCollection
select a.CaseNumberKey, a.SearchUserID, c.DocType, c.RelatedDocID
from documents c
JOIN   newCityCollection.dbo.PropertyInformation a ON C.CaseNumberKey = A.CaseNumberKey
where  c.DocType = 'Assignment' and c.RelatedDocID is null and a.ClientKey = 3 and (count(c.docID) > 3)

Upvotes: 0

Views: 42

Answers (1)

Uberzen1
Uberzen1

Reputation: 415

Remove the last part of your WHERE clause and add a having clause: 

  USE newCityCollection
SELECT a.CaseNumberKey, a.SearchUserID, c.DocType, c.RelatedDocID
FROM documents c
JOIN   newCityCollection.dbo.PropertyInformation a ON C.CaseNumberKey = A.CaseNumberKey
WHERE c.DocType = 'Assignment' and c.RelatedDocID is null and a.ClientKey = 3
GROUP BY a.CaseNumberKey, a.SearchUserID, c.DocType, c.RelatedDocID
HAVING count(c.docID) > 3

Upvotes: 1

Related Questions