Finding MIN(1) date in a data extension - error message about aggregate functions

Question

I'm currently trying to find a list of email Addresses with a particular date in a data extension. The data extension can contain multiple instances of the same email address, but with different dates (the dates refer to campaigns that these people have booked). I'm using the below query.

SELECT emailAddress, creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)

The final two AND clauses aren't important so you can ignore those.

We're using this to send a daily email. I would like to refine this search so it retrieves only those where the MINIMUM value of creative Date found for them meets the WHERE criteria.

So if a person appears in there twice, with 16/08/2018 and 17/08/2018, I only want them to appear in there once - when 16/08/2018 is 49 days away, but not when 17/08/2018 is 49 days away.

When I try to run

SELECT emailAddress, MIN(1)creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)

I get an error saying emailAddress is not in an aggregate function. Can anyone help?

Answer 1

When you use an agregate function (e.g COUNT, MIN, AVERAGE, MAX), you have to finish the statement with a GROUP BY clause and include any column that is not agregrated.

In your case it would be

SELECT emailAddress, MIN(1)creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)
GROUP BY emailAddress

Reference:

• Why do we need GROUP BY in aggregate functions.
• Aggregate functions