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

Question

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

The error message is quite clear. You are using a column in the correlation clause of a subquery in the `SELECT` and it is not aggregated. What you want to accomplish is not clear. Sample data, desired results, and a clear explanation would help. You might also be able to simplify the example. I doubt that six tables are needed to illustrate the issue. — Gordon Linoff, Jun 03 '21 at 00:19
When you add "GROUP BY" to a SELECT statement, ANY column that you use as either output results (from the main "SELECT" portion of the query) or as part of the WHERE clause (or, in most cases, as a "JOIN"ing column) must either be one of the columns included in the "GROUP BY", or must otherwise be aggregated in some way. The reference docs describing "GROUP BY" for your RDBMS - in your case, SQL Server - is really the best place to start to understand the basic rules behind using GROUP BY. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15 — Craig, Jun 03 '21 at 00:25
Does this answer your question? [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Charlieface, Jun 03 '21 at 11:39
Related: `CONVERT(DATE , DO.ProcessedDateTimeStamp)` Since you use this, i'll point you to this [might not be efficient discussion](https://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea) — SMor, Jun 03 '21 at 11:44

score 1 · Answer 1 · answered Jun 03 '21 at 04:38

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;

score 0 · Answer 2 · answered Jun 03 '21 at 04:34

0

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

answered Jun 03 '21 at 04:34

twmp

75
2
7

score 0 · Answer 3 · answered Jun 03 '21 at 06:19

0

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

answered Jun 03 '21 at 06:19

Chris

35
1
7

Please put your working query. It will be helpful for others in future. also, mark your answer as the accepted answer. – Venkataraman R Jun 03 '21 at 08:44

score 0 · Answer 4 · edited Nov 04 '21 at 19:32

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

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

4 Answers4