I have 2 data extensions. DE A has Account IDs and it is the primary key. DE B has Lead ID, but this is not the primary key. DE B has a column called 'Amount'. For those A.[Account ID] = B.[Lead ID] I want to calculate the sum total of Amount. I want to insert the final output for each account ID into a third DE. I am trying the below query:
SELECT A.[Account ID] AS [Account ID], A.[Created Date Backend] AS [Created Date], sum(B.[Amount]) AS [Amount] FROM [DE A] AS A INNER JOIN [DE B] AS B ON A.[Account ID] = B.[Lead ID] GROUP BY B.[Lead ID]
Got the following error:
Column 'C7229974.A.Account ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.