CODEWITHSUNDEEP
sqlsql-servert-sql
Chris
Chris

Reputation: 45

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

Getting the error of:

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

Have tried other stuff as you can see from my comments in my code not sure how to solve this.

If someone could explain the problem as well so I could understand that would be awesome too.

Thanks

DECLARE @TodaysDate DATE
SET @TodaysDate = '2021-06-01' 

SELECT 
DO.ObjectIdentifier AS BoxId,
--FBC.ContentType,
--FBC.Status AS DestroyStatus,
DO.ProcessedDateTimeStamp,
(SELECT COUNT(*) FROM FM8HO_FirmBoxContent
WHERE BoxIdentifier = DO.ObjectIdentifier) AS NumFilesInBox,
--(SELECT COUNT(DO1.ObjectIdentifier) FROM FM8HO_DestructionObjects AS DO1
--WHERE DO1.ObjectIdentifier = DO.ObjectIdentifier)
COUNT(DO.ObjectIdentifier) AS FilesMarkedForDestroyInBox,
--CASE WHEN (SELECT COUNT(*) FROM FM8HO_FirmBoxContent FBC.BoxIdentifier = COUNT(DO.ObjectIdentifier) THEN 'Yes' ELSE 'NO' END AS CanRetrieve
--CASE WHEN SELECT COUNT(*) FROM FirmBo WHERE FBC.BoxIdentifier = DO.ObjectIdentifier = COUNT(DO.ObjectIdentifier) THEN 'Yes' ELSE 'NO' END AS CanRetrieve
CASE WHEN (SELECT COUNT(*) FROM FM8HO_FirmBoxContent WHERE FBC.BoxIdentifier = DO.ObjectIdentifier) = COUNT(DO.ObjectIdentifier) THEN 'YES' ELSE 'NO' END
FROM FM8HO_DestructionObjects AS DO
LEFT JOIN FM8HO_FirmBoxes AS FB ON FB.BoxIdentifier = DO.ObjectIdentifier
LEFT JOIN FM8HO_ArchiveBoxes AS AB ON AB.BoxIdentifier = DO.ObjectIdentifier
LEFT JOIN FM8HO_FirmBoxContent AS FBC ON FBC.BoxIdentifier = DO.ObjectIdentifier
WHERE CONVERT(DATE , DO.ProcessedDateTimeStamp) = @TodaysDate AND FBC.Status = 'Destroyed' --AND FBC.Status = 'Destroyed' AND 
GROUP BY DO.ObjectIdentifier, FBC.ContentType, FBC.Status, DO.ProcessedDateTimeStamp

Upvotes: 0

Views: 5005

Answers (4)

kargarf
kargarf

Reputation: 66

If there is a column name like (name, state ..) which there maybe a method/function in this name, you may get this error. in my case it was such that. so to bring that column in query you may use a subquery like this:

select 
    (select count(*) from secinccaeb) as GrandTotal, 
    count(a.evtye) as PartialTotal, 
    (select name from hlsyslistitem 
     where listitemid = c.listitemid) as EventType 
from 
    secinccaeb as a
inner join 
    hlsyslistitem as c on c.listitemid = a.evtye
group by 
    c.listitemid

Upvotes: 0

Chris
Chris

Reputation: 45

All good solved it by adding a column to the GROUP BY

Upvotes: 0

SteveC
SteveC

Reputation: 6015

In the code posted nested SELECT statement(s) create correlated subqueries. Because the column(s) in the subquery aren't either in an aggregate function or the GROUP BY clause it throws an error. One way to calculate the NumFilesInBox column is to use OUTER APPLY. You could try something like this

SELECT DO.ObjectIdentifier AS BoxId,
       FBC.ContentType,
       FBC.[Status] AS DestroyStatus,
       DO.ProcessedDateTimeStamp,
       isnull(fbc.NumFilesInBox, 0) AS NumFilesInBox,
       COUNT(DO.ObjectIdentifier) AS FilesMarkedForDestroyInBox,
       CASE WHEN isnull(fbc.NumFilesInBox, 0) = COUNT(DO.ObjectIdentifier) THEN 'Yes' ELSE 'NO' END AS CanRetrieve
FROM FM8HO_DestructionObjects AS DO
     LEFT JOIN FM8HO_FirmBoxContent AS FBC ON FBC.BoxIdentifier = DO.ObjectIdentifier
     outer apply (SELECT COUNT(*) FROM FM8HO_FirmBoxContent fbc WHERE fbc.BoxIdentifier = DO.ObjectIdentifier) AS fbc(NumFilesInBox)
WHERE CONVERT(DATE , DO.ProcessedDateTimeStamp) = @TodaysDate 
      AND FBC.Status = 'Destroyed' 
GROUP BY DO.ObjectIdentifier, FBC.ContentType, FBC.[Status], DO.ProcessedDateTimeStamp;

Upvotes: 1

twmp
twmp

Reputation: 115

Have you tried window clause instead of group by ? This won't restrict you on selected columns As comments mentioned we would need more details and preferably simplified example

Upvotes: 0

Related Questions