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

2
votes

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?

1
sqlsql-server
What don't you understand about the error? Use a having clause. That said, I have no idea why you would need a max() in this case. - Gordon Linoff
What exactly are you trying to achieve? Can you please share your table structures, some sample data and the result you're trying to get for this sample? - Mureinik
What exactly are you expecting to obtain with max(A.StudentFirstName) like 'A%'??? That doesn't even make sense. Seems to me you're so far off-track in trying to solve a problem that you're just grasping for anything you can think of. It would probably be much better to explain the actual problem you're trying to solve, with some sample data and the output you're trying to obtain from that data, and ask how to write the query to do so. - Ken White

1 Answers

9
votes

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%'