How to include other columns in nested Select statement with

Mark Tabor

10y899

I have a select statement and in that i have a nested select statement , and in nested select statement i have aggregate function on one column , I want to add more columns with this nested select statement , and i just want to group by on one column ,

check the below query

SELECT t1.DynamicIndicatorID,t1.District,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID,x.filenameName as FileName

FROM DynamicIndicatorsTargetsForPu t1

LEFT JOIN (

SELECT IndicatorID,filenameName, MAX(fileID) AS fileid

FROM tblUploadedFiledetailsForPartners group by IndicatorID

) x ON t1.DynamicIndicatorID = x.IndicatorID where t1.UserId=59

I am getting an error

Msg 8120, Level 16, State 1, Line 4

Column 'tblUploadedFiledetailsForPartners.filenameName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I want to add more columns in Nested select statement as i have to show these columns as well but i want to group by on just one column .

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Sorry, just remove that column from inner query, which we have now taken outer query:
SELECT t1.DynamicIndicatorID
,t1.District
,t1.Quarter
,t1.District
,t1.TFA
,t1.TFB
,t1.TFG
,t1.TFW
,t1.TFM
,x.fileID as fileID
,uffp.filenameName as FileName
FROM DynamicIndicatorsTargetsForPu t1
LEFT JOIN (
SELECT IndicatorID
,MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners
group by IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID
left outer join tblUploadedFiledetailsForPartners uffp on x.fileID = uffp.fileID
where t1.UserId=59
Mark Tabor10y ago
Nitin Sontakke
not working give that error
Msg 8120, Level 16, State 1, Line 15
Column 'tblUploadedFiledetailsForPartners.filenameName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Nitin Sontakke10y ago
SELECT t1.DynamicIndicatorID
,t1.District
,t1.Quarter
,t1.District
,t1.TFA
,t1.TFB
,t1.TFG
,t1.TFW
,t1.TFM
,x.fileID as fileID
,uffp.filenameName as FileName
FROM DynamicIndicatorsTargetsForPu t1
LEFT JOIN (
SELECT IndicatorID
,filenameName
,MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners
group by IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID
left outer join tblUploadedFiledetailsForPartners uffp on x.fileID = uffp.fileID
where t1.UserId=59
Nilesh Patel10y ago
SELECT t1.DynamicIndicatorID,t1.District,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID,x.filenameName as FileName
FROM DynamicIndicatorsTargetsForPu t1
LEFT JOIN (
SELECT IndicatorID,filenameName, MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners group by IndicatorID ,filenameName
) x ON t1.DynamicIndicatorID = x.IndicatorID where t1.UserId=59

Please Use this Code you will get output

Here I will Added new Group by filenameName

When are you use any aggregate function in Select Query that time you must be put other column name in Group By
Nitin Sontakke10y ago
Hello,
All non-aggregate columns MUST BE included in group by clause.
If you want more columns, but donot want them to include them in group by, you can again left outer join tblUploadedFiledetailsForPartners table out side of the inner query.
Hope you get it.
Mark Tabor10y ago
how to do that