1

I have created a Data Extension (account) which contains an account_id and various boolean fields and email address, I want to create a field which stores the last opened date from _Opened table via left join.

Query:

SELECT 
    a.SubscriberKey,
    a.email_address,
    a.is_active,
    MAX(o.EventDate) as last_opened_date
from account a
left join _Open o
on o.SubscriberKey = a.SubscriberKey
GROUP BY a.SubscriberKey

I'm getting this error however

An error occurred while checking the query syntax. Errors: Column 'C10959787.account.email_address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone tell me what I'm doing wrong?

Improve this question
3
  • The group by works if I remove the non aggregated fields (email_address, is_active), but I need to retrieve these as they are not nullable in my data extension. – Deployment Failure Aug 2 '17 at 0:47
  • if you avoid using GROUP BY, you should be able to use non-aggregated fields. – Brad Sapkota Aug 2 '17 at 0:55
  • I tried using a subquery but ORDER BY clause is not available – Deployment Failure Aug 2 '17 at 0:56
2

You cannot obtain additional fields in a GROUP BY SQL Query unless you aggregate them with the SubscriberKey like so,

SELECT 
a.SubscriberKey,
a.email_address,
a.is_active,
MAX(o.EventDate) AS last_opened_date
FROM account a
LEFT JOIN _Open o
ON o.SubscriberKey = a.SubscriberKey
GROUP BY a.SubscriberKey, a.email_address, a.is_active

This will allow you to get the correct MAX date because it will be able to group all records successfully.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.