Reason For Column Is Invalid in the Select List Because It Is Not Contained in Either an Aggregate Function or the Group by Clause

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

Suppose I have the following table T:

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

And I do the following query:

SELECT a, b
FROM T
GROUP BY a

The output should have two rows, one row where a=1 and a second row where a=2.

But what should the value of b show on each of these two rows? There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group. It's ambiguous.

This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.).

Fixing it might look like this:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Now it's clear that you want the following result:

a   x
--------
1   ghi
2   pqr

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

Put in other words, this error is telling you that SQL Server does not know which B to select from the group.

Either you want to select one specific value (e.g. the MIN, SUM, or AVG) in which case you would use the appropriate aggregate function, or you want to select every value as a new row (i.e. including B in the GROUP BY field list).

Consider the following data:

The query

SELECT A, COUNT(B) AS T1 
FROM T2 
GROUP BY A

would return:


A  T1
1  2
2  3

which is all well and good.

However consider the following (illegal) query, which would produce this error:

SELECT A, COUNT(B) AS T1, B 
FROM T2 
GROUP BY A

And its returned data set illustrating the problem:


A  T1  B
1  2   13? 79? Both 13 and 79 as separate rows? (13+79=92)? ...?
2  3   13? 42? ...?

However, the following two queries make this clear, and will not cause the error:

Using an aggregate

SELECT A, COUNT(B) AS T1, SUM(B) AS B
FROM T2
GROUP BY A

would return:


A  T1  B
1  2   92
2  3   68

Adding the column to the GROUP BY list

SELECT A, COUNT(B) AS T1, B
FROM T2
GROUP BY A, B

would return:

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

The error says it all:

...Employee First Name' is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause

Saying that, there are other columns that need attention too.

Either reduce the columns returned to only those needed or include the columns in your GROUP BY clause or add aggregate functions (MIN/MAX). Also, your WHERE clause should be placed before the GROUP BY.

Try:

select   distinct [employee number]
      ,[Employee First Name]
      ,[Employee Last Name]
      ,min([DateTime])
      ,[Card Number]
      ,min([Reader Name])
from [Interface].[dbo].[VwEmpSwipeDetail] 
where CAST([datetime] AS DATE)=CAST(GETDATE() AS DATE)
group by  [employee number], [Employee First Name], [Employee Last Name], [Card Number]

I've removed status and location as this is likely to return non-distinct values. In order to return this data, you may need a subquery (or CTE) that first gets the unique IDs of the SwipeDetails table, and from this list you can join on to the other data, something like:

SELECT [employee number],[Employee First Name],[Employee Last Name].. -- other columns
FROM [YOUR_TABLE]
WHERE SwipeDetailID IN (SELECT MIN(SwipeDetailsId) as SwipeId
                        FROM SwipeDetailTable
                        WHERE CAST([datetime] AS DATE)=CAST(GETDATE() AS DATE)
                        GROUP BY [employee number])

Column not contained in either an aggregate function or a GROUP BY clause

You want to deal with the maximum inventory quantity per product. But you are joining all inventory rows, where you should only pick the maximum quantity rows.

This can be done with a lateral join, if your DBMS supports this (you have forgotton to tell us which you are using) or simply by joining the rows in question by applying a window function as follows.

SELECT 
    A.mrno, A.remarks, 
    B.itemcode, B.description, B.uom, B.quantity, 
    C.whsecode, C.whseqty, D.rate 
FROM 
    Mrhdr A
INNER JOIN 
    Mrdtls B ON A.mrno = B.mrno
INNER JOIN 
(
    SELECT
      itemcode, whsecode, quantity as whseqty,
      MAX(quantity) OVER (PARTITION BY itemcode) AS max_qty
    FROM inventoryTable
) C ON B.itemcode = C.itemcode AND C.whseqty = C.max_qty
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    A.mrno = @MRNo AND B.quantity < C.whseqty;

This query should work in most DBMS. If you are working with a DBMS that supports the standard SQL FETCH WITH TIES clause, I'd change the join to:

INNER JOIN 
(
    SELECT itemcode, whsecode, quantity as whseqty
    FROM inventoryTable
    ORDER BY RANK() OVER (PARTITION BY itemcode ORDER BY quantity DESC)
    FETCH FIRST ROW WITH TIES
) C ON B.itemcode = C.itemcode

so as to only select the top rows inside the subquery already and not to awkwardly filter them later. But well, a lateral join may even be considered more straight-forward here.

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

I think its because of EmployeeID column missing in group by clause. Columns specified in sub-query also should be included in group by clause.

Please Try:

select a.EmployeeName, 

STUFF(( SELECT ',' + camTable.DepartmentName AS [text()]
                        FROM EmpoyeeDepartment subTable
                left join DepartmentTable camTable on subTable.DepartmentID = camTable.DepartmentID 
                        WHERE
                        subTable.EmployeeID = a.EmployeeID
                        FOR XML PATH('')
                        ), 1, 1, '' )
            AS Departments
from 
EmployeeTable a
where a.EmployeeID = 144025
group by EmployeeID, EmployeeName, Departments

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

Add Nama to the group by

SELECT Tabel1.NIK, Tabel1.Nama, AVG(Table2.Nilai) AS RataRata FROM Tabel1, Table2 WHERE Tabel1.NIK = Table2.NIK GROUP BY Tabel1.NIK, Tabel1.Nama

Or remove Nama from the select

SELECT Tabel1.NIK, AVG(Table2.Nilai) AS RataRata FROM Tabel1, Table2 WHERE Tabel1.NIK = Table2.NIK GROUP BY Tabel1.NIK

Reason For Column Is Invalid in the Select List Because It Is Not Contained in Either an Aggregate Function or the Group by Clause