Adding an aggregate function to the WHERE clause? [duplicate]

Question

I want to make a report that tells all the customers that haven't been called in the last 75 or so days. My columns are as follows.

Customer# Customer_Name Phone_Number Call_Date Salesman

The call date pulls up the dates of any and every time the customer has been called.

Here's my current query.

select customer_no
      ,Customer_name
      ,Phone_number
      ,max(Call_Date) as Call_date
      ,Salesman
from salescalls
where call_date <= current_date - 75

The problem I'm having is that it is pulling every single customer and using the last time they were called 75 or more days ago.

For example when the last call date is 6/4/14, it pulls the number up and lists the call date as 11/10/13.

It isn't supposed to list customers that have been called within the last 75 days. So to prevent this, I'm trying to do this in the where clause.

Where max(call_date) <= current_date - 75

But this just gives me an error:

aggregates not allowed in WHERE clause

Answer 1

You want a having clause:

select customer_no, Customer_name, Phone_number, max(Call_Date) as Call_date,
       Salesman
from salescalls
group by customer_no, Customer_name, Phone_number, Salesman
having max(call_date) <= current_date - 75;

You can't put aggregation functions in a where clause.

Answer 2

You need to put your condition in HAVING clause.

having max(call_date) <= current_date - 75