[SOLVED] Aggregate may not appear in WHERE clause

Aggregate may not appear in WHERE clause

I have this query

SELECT
    a.Campaign,
    COUNT(DISTINCT a.Customer) AS 'Records',
    MIN(a.LastRecord) AS 'First Record',
    MAX(a.LastRecord) AS 'Last Record',
    MAX(a.daytime) AS 'Records up to'
FROM 
    contacts.dbo.contacts_ALL a 
JOIN 
    sms b ON a.Customer = b.Customer
WHERE 
    a.LastRecord >= MIN(b.smsDelivered)
GROUP BY 
    a.Campaign
HAVING 
    a.LastRecord >= MIN(b.smsDelivered)
ORDER BY 
    a.Campaign DESC, [First Record]

I get this error from SQL Server:

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.

What might be wrong there?

Solution

What might be wrong there?

The optimizer doesn't know what is the value of MIN(b.smsDelivered) since the WHERE clause is executed before SELECT.

Order of Execution

1. FROM/JOIN
    
2. WHERE
    
3. GROUP BY
    
4. HAVING
    
5. SELECT
    
6. ORDER BY
    
7. LIMIT/OFFSET

Either use a subquery and remove the HAVING clause

 WHERE 
    a.LastRecord >= ( SELECT MIN(s.smsDelivered) FROM sms s )

or remove the WHERE clause and add an aggregate function into the HAVING clause.