TSQL / SQL - Error: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I'm hoping someone can point me in the right direction here.

DB #1 NAME: "Wayin_Integration_SFMC_AD"

And I'm trying to merge with this Master table (DB #2): "Master_Users_SVOC_test_vt"

Assuming everyone in "Wayin_Integration_SFMC_AD" already has a primary key and record in "Master_Users_SVOC_test_vt", now I want to MERGE INTO the remaining data for that record.

Problem is, most of my fields aren't included in the GROUP BY clause.

Do I need to use a subquery here? I am very new to SQL. Here is the snippet that I am unsure of. Without the GROUP BY this would work, but it's the grouping that is confusing me.

ERROR MESSAGE: "Column 'Wayin_Integration_SFMC_AD.First_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. "

which is the first field not included in the GROUP BY clause.

THIS IS THE SQL THAT ISN'T WORKING:

MERGE INTO [bo_marketing_capability_dev].[dbo].[Master_Users_SVOC_test_vt] AS m 
USING (
SELECT
m.subscriber_key,
m.email_address,
w.[First_Name] AS first_name,
w.[Last_Name] AS last_name,
, MAX (w.DateAdded) AS wayin_DateAdded    
FROM Wayin_Integration_SFMC_AD w 
INNER JOIN Master_Users_SVOC_test_vt m
ON w.Email = m.email_address
GROUP BY subscriber_key, w.[Email], m.email_address
) AS SRC
ON ([SRC].[email_address] = [m].[email_address])

You should be able to provide a simpler query that has the same problem. — Gordon Linoff
If you want to use aggregate functions, you need to group by all the columns other than those having those functions — Srinika Pinnaduwage

Jay Desai Jay Desai · Accepted Answer · 2020-05-11T00:12:50

MERGE INTO [bo_marketing_capability_dev].[dbo].[Master_Users_SVOC_test_vt] AS m 
USING (
SELECT
m.subscriber_key,
m.email_address,
w.[First_Name] AS first_name,
w.[Last_Name] AS last_name,
, MAX (w.DateAdded) AS wayin_DateAdded    
FROM Wayin_Integration_SFMC_AD w 
INNER JOIN Master_Users_SVOC_test_vt m
ON w.Email = m.email_address
GROUP BY m.subscriber_key, m.email_address, w.[First_Name], w.[Last_Name]
) AS SRC
ON ([SRC].[email_address] = [m].[email_address])

Try above code. GROUP BY should have all non aggregate columns.

TSQL / SQL - Error: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

1 Answers