CODEWITHSUNDEEP
sqlsql-server
K.Suthagar
K.Suthagar

Reputation: 2306

An aggregate may not appear in the WHERE clause( SQL Server Error )

When I tried this query,

select  
    (A.StudentId),
     max(A.StudentFirstName),
     max(A.StudentLastName),
     max(A.StudentAddress),
     'Batch ' + max(C.BatchName),
     CAST(MAX(CAST(A.StudentStatus as INT)) AS BIT),
     max(B.StudentBatchId) 
from 
    tblStudentDetails A  
inner join 
    tblStudentBatchDetails B on A.StudentId = B.studentid 
inner join 
    tblBatch C on C.BatchId = B.batchid 
where 
    max(A.StudentFirstName) like 'A%'
group by 
    A.StudentId

I got this 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.

Can someone help to recover this problem?

Upvotes: 3

Views: 23068

Answers (1)

Spock
Spock

Reputation: 4910

The correct syntax would be...

select  (A.StudentId),max(A.StudentFirstName),
max(A.StudentLastName),max(A.StudentAddress),
'Batch ' + max(C.BatchName),CAST(MAX(CAST(A.StudentStatus as INT)) AS BIT),
max(B.StudentBatchId) 
from tblStudentDetails A  
inner join tblStudentBatchDetails B on A.StudentId=B.studentid 
inner join tblBatch C on C.BatchId=B.batchid 
group by A.StudentId
having max(A.StudentFirstName) like 'A%'

Upvotes: 10

Related Questions