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?

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. Commented Aug 2, 2017 at 0:47
  • if you avoid using GROUP BY, you should be able to use non-aggregated fields. Commented Aug 2, 2017 at 0:55
  • I tried using a subquery but ORDER BY clause is not available Commented Aug 2, 2017 at 0:56

1 Answer 1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .