CODEWITHSUNDEEP
sql-serversubquery
Ashley Elizabeth
Ashley Elizabeth

Reputation: 1

Subquery distinct field

Hello I am learning to write sql queries and I am trying to query a ledger table to SELECT a field "ENCID" where there are more than 4 distinct values in a separate file "TDATE" for each distinct ENCID. Then filter by a 3rd field "ITEMTYPE"

This is what I have:

SELECT 
      [ENCID]
      ,[PATIENTID]
      ,[ITEMTYPE]
      ,[Service Date]
      ,[Transaction Date]
      ,[Trans]
      ,[PracticeName]

  FROM TABLE1
WHERE ITEMTYPE = 'S'
AND ENCID IN (SELECT ENCID FROM TABLE1 WHERE Count(Distinct [Transaction Date]) >4 
AND ITEMTYPE = 'S')

I am getting this error "DataSource.Error: Microsoft SQL: 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."

Upvotes: 0

Views: 59

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

Try this instead:

SELECT 
      [ENCID]
      ,[PATIENTID]
      ,[ITEMTYPE]
      ,[Service Date]
      ,[Transaction Date]
      ,[Trans]
      ,[PracticeName]    
FROM TABLE1
WHERE ITEMTYPE = 'S'
   AND ENCID IN (
      SELECT ENCID 
      FROM TABLE1 
      WHERE ITEMTYPE = 'S'
      GROUP BY ENCID
      HAVING Count(Distinct [Transaction Date]) >4 
         AND MAX ([Transaction Date]) - MIN ([Transaction Date]) > 60
     )

Generally, aggregate functions can only be used in SELECT, HAVING, and ORDER BY clauses (as WHERE determines exactly which records are being aggregated).

What the error message is surprisingly elaborating on is the unique cases where WHERE may contain an aggregate function. Such as this:

SELECT a.id
FROM a
GROUP BY a.id
HAVING a.id IN (
  SELECT b.a_id
  FROM b
  WHERE b.total = COUNT(a.something)
)

Upvotes: 1

Related Questions