Aggregating Marketing Cloud Data Views

Question

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?

Answer 1

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.