Query: Can inner join be used together with Group by clause

Question

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.

Answer 1

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]