CODEWITHSUNDEEP
sqlsql-servergroup-byaggregate
user1557020
user1557020

Reputation: 301

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

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.

Upvotes: 7

Views: 72854

Answers (4)

Navneet
Navneet

Reputation: 447

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();

Upvotes: 5

Tanner
Tanner

Reputation: 22733

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

Upvotes: 8

Glenn
Glenn

Reputation: 9150

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]

Upvotes: 4

Giles
Giles

Reputation: 1667

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.

Upvotes: 2

Related Questions