0

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.

  • 2
    Hey Mit88 - w3schools is a great place to learn and get started with SQL. I understand you are just starting out, but I suggest you have a look there and go through the basics before asking SQL questions of the Salesforce community. – Cameron Robert Mar 23 '17 at 0:41
5

Reference: https://www.w3schools.com/sql/sql_groupby.asp

You need to include every value in your SELECT statement that is NOT on the list of aggregate functions (COUNT, MAX, MIN, SUM, AVG)

Try:

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 A.[Account ID],A.[Created Date Backend]
| 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.