CODEWITHSUNDEEP
sql-servergroup-byaggregate-functionssql-server-2014dynamicquery
Add080bbA
Add080bbA

Reputation: 1876

Group by or aggregate error even though field is included in group by list

I have an SQL multiple value grouping query in which I want to find the maximum of the field slt.OUTCOST. Running it results in the following error:

Column '#eldekiSeri.CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I get this error even though that field is included in the GROUP BY clause.

If I delete the MAX aggregate and include slt.OUTCOST in the group list, the error goes away but then I can't find the maximum for the field anymore.

This is my SQL:

set @sqlAbuk = '
SELECT
eldekiSeri.CODE AS [SERİ NO], 
eldekiSeri.KODU AS [STOK KODU],
eldekiSeri.ACIKLAMA AS [STOK ADI],
clc.CODE AS [CARİ KOD], 
clc.DEFINITION_ AS [CARİ ADI],
MAX(slt.OUTCOST) AS [ALIŞ FİYATI],
stf.DATE_ AS [ALIŞ TARİHİ],
eldekiSeri.AMBARNO AS [ŞUBE KODU]
FROM '+@firmDb+'.dbo.LG_'+@firmNumber+'_'+@firmPeriod+'_SERILOTN sl
INNER JOIN '+@firmDb+'.dbo.LG_'+@firmNumber+'_'+@firmPeriod+'_SLTRANS slt ON sl.LOGICALREF = slt.SLREF
INNER JOIN #eldekiSeri eldekiSeri ON sl.CODE = eldekiSeri.CODE
INNER JOIN '+@firmDb+'.dbo.LG_'+@firmNumber+'_'+@firmPeriod+'_STFICHE stf ON slt.STFICHEREF = stf.LOGICALREF
INNER JOIN '+@firmDb+'.dbo.LG_'+@firmNumber+'_CLCARD clc ON stf.CLIENTREF = clc.LOGICALREF
WHERE (slt.INVENNO = 0) AND (slt.FICHETYPE = 1)';
--print @sqlAbuk;
--exec (@sqlAbuk);
set @sqlAbuk = @sqlAbuk +' AND eldekiSeri.CODE not in ( select distinct bc.[SERİ NO] from #birlestirilmisClone bc ) ';
set @sqlAbuk = @sqlAbuk +' GROUP BY eldekiSeri.CODE, eldekiSeri.KODU, eldekiSeri.ACIKLAMA, clc.CODE, clc.DEFINITION_, stf.DATE_, eldekiSeri.AMBARNO';
set @sqlAbuk = @sqlAbuk +' ORDER BY eldekiSeri.CODE, eldekiSeri.KODU, eldekiSeri.ACIKLAMA, clc.CODE, clc.DEFINITION_, slt.OUTCOST, stf.DATE_, eldekiSeri.AMBARNO';
print @sqlAbuk;
--Exec(@sqlAbuk);
insert into #birlestirilmis Exec(@sqlAbuk);
--print (@sqlAbuk);

Upvotes: 0

Views: 93

Answers (1)

Ricky Keane
Ricky Keane

Reputation: 1700

It is because you have slt.OUTCOST in your ORDER BY clause. Remove this and it will get rid of your error.

Upvotes: 1

Related Questions