The column is not valid in the select list because it is neither contained in the aggregate function nor in the GROUP BY clause

Question

We have a table that will capture each napkin record employee. I am trying to write a query to get a list of an individual employee record by making the first miss to date.

We save the scroll date information in a column datetime. Here is my request - this is an exception to the 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:

The column "Interface.dbo.VwEmpSwipeDetail.Employee First Name" is not allowed in the selection list because it is not contained in the aggregate function or in the GROUP BY clause.

Any help please?

Thanks in advance.

Answer 1

:

... , , GROUP BY

, , .

, , GROUP BY, (MIN/MAX). , WHERE 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]

status location, , , . , ( CTE), SwipeDetails, , :

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

Answer 2

, :

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

Answer 3

(CURDATE), , :

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]

Answer 4

mysql, mysql.

sql-server , [Employee First Name] , ( ). min/max . , GROUP BY, (EG min) .