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

Question

We have a table which will capture the swipe record of each employee. I am trying to write a query to fetch the list of distinct employee record by the first swipe for today.

We are saving the swipe date info in datetime column. Here is my query its throwing exception.

 select distinct 
    [employee number], [Employee First Name]
    ,[Employee Last Name]
    ,min([DateTime])
    ,[Card Number]
    ,[Reader Name]
    ,[Status]
    ,[Location] 
from 
    [Interface].[dbo].[VwEmpSwipeDetail] 
group by  
    [employee number] 
where 
    [datetime] = CURDATE();

Getting error:

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

Any help please?

Thanks in advance.

when using aggregate functions like `min` all the other fields within `select` should be either within aggregate functions or should be mentioned in `group by` — Dmitry Bychenko, Sep 09 '14 at 10:32
possible duplicate of [Column 'dbo.User.FB\_UserId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/18310391/column-dbo-user-fb-userid-is-invalid-in-the-select-list-because-it-is-not-cont) — Bulat, Sep 09 '14 at 10:32
search for this error message son SO, you will find many questions like this: http://stackoverflow.com/questions/18310391/column-dbo-user-fb-userid-is-invalid-in-the-select-list-because-it-is-not-cont — Bulat, Sep 09 '14 at 10:33
possible duplicate of this [question](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) — jomsk1e, Sep 09 '14 at 10:45

Tanner · Accepted Answer · 2014-09-09T10:56:41.300

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])

getting error 'CURDATE' is not a recognized built-in function name. — user1557020, Sep 09 '14 at 10:53

Navneet · Answer 2 · 2014-09-10T08:37:37.983

5

Please Try Below Query :

select   distinct [employee number],[Employee First Name]
          ,[Employee Last Name]
          ,min([DateTime])
          ,[Card Number]
          ,[Reader Name]
          ,[Status]
          ,[Location] from [Interface].[dbo].[VwEmpSwipeDetail] group by [employee number],[Employee First Name]
          ,[Employee Last Name]
          ,[Card Number]
          ,[Reader Name]
          ,[Status]
          ,[Location] having [datetime]=GetDate();

edited Sep 10 '14 at 08:37

answered Sep 09 '14 at 10:38

Navneet

447
4
13

1

throwing exception in where condition – user1557020 Sep 09 '14 at 10:42
1

Check now actually i used where condition besides having where condition was not worked in group by Now Try new query i updated Now. – Navneet Sep 10 '14 at 08:38

score 4 · Answer 3 · answered Sep 09 '14 at 10:45

First find the first timestamp for each employee on the given day (CURDATE), then join back to the main table to get all the details:

WITH x AS (
    SELECT [employee number], MIN([datetime] AS minDate
      FROM [Interface].[dbo].[VwEmpSwipeDetail]
      WHERE CAST([datetime] AS DATE) = CURDATE()
      GROUP BY [employee number]
)
select [employee number]
      ,[Employee First Name]
      ,[Employee Last Name]
      ,[DateTime]
      ,[Card Number]
      ,[Reader Name]
      ,[Status]
      ,[Location]
  from [Interface].[dbo].[VwEmpSwipeDetail] y
  JOIN x ON (x.[employee number] = y.[employee number] AND x.[minDate] =Y.[datetime]

score 2 · Answer 4 · answered Sep 09 '14 at 10:34

2

This should not be marked as mysql as this would not happen in mysql.

sql-server does not know which of the grouped [Employee First Name] values to return so you need to add an aggregate (even if you only actually expect one result). min/max will both work in that case. The same would apply to all the other rows where they are not in the GROUP BY or have an aggregate function (EG min) around them.

answered Sep 09 '14 at 10:34

Giles

1,597
11
15

1

mysql incidentally is more easy going, it would return the first value it finds if an aggregate function is not used. This can cause it`s own problems however. – Giles Sep 09 '14 at 10:36

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

4 Answers4

Linked