How to fix this error "is invalid in the select list because it is not contained in..."

Question

I have this code in SQL:

SELECT 
    COUNT(*) as t,
    t1.EMPLID, 
    t3.DNI,
    t1.PUNCH_DTTM,
    PUNCH_TYPE,
    TCD_ID,
    t2.PCADescripcion,
    t2.DFIDescripcion,
    t2.Alias,
    t5.apellido,
    t4.planta
FROM 
    [MatrizDB].[db_owner].[Historial_TRH] t1
LEFT JOIN 
    MatrizDB.db_owner.Dispositivos_TRH t2 ON t2.RefId = TCD_ID
LEFT JOIN 
    MatrizDB.db_owner.Pipolsoft_Legajos t3 ON t3.EMPLID = t1.EMPLID
LEFT JOIN 
    [MatrizDB].[dbo].[nomina_fase_two] t4 ON t4.dni_id = t3.DNI 
LEFT JOIN 
    [MatrizDB].dbo.v_nomina t5 ON t5.nroDocumento = t4.dni_id
WHERE  
    CONVERT(VARCHAR(25), PUNCH_DTTM, 120) LIKE '2019-04-24%' 
    AND t4.dni_responsable = 30329134 
    AND PUNCH_TYPE = 5

When I execute the query, I get this error

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

Please provide sample data, desired results, an explanation of what you want to do, and an appropriate database tag. — Gordon Linoff, Apr 24 '19 at 10:45
See [the many questions already asked](https://stackoverflow.com/search?q=%5Bsql-server%5D+%22is+invalid+in+the+select+list+because+it+is+not+contained+in%22) — , Apr 24 '19 at 10:46
Welcome do SO, pls before posting a question make a search on SO, Google, SQL Documentation. Seriously, you just forgot the group by clause. Also read the help section with tips to write a good question — jean, Apr 24 '19 at 10:48

score 4 · Answer 1 · edited Jan 03 '22 at 10:05

You have count(*) in the select, so your query is an aggregation query. You have no group by; an aggregation query with no group by returns exactly one row. What values should be in that row for the other columns?

One answer is to put all the non-aggregated columns in the group by. Another is to remove all the non-aggregated columns from the select. Another would be to replace the count(*) with a window function (say, count(*) over () or what I suspect you want is row_number() over (order by <some column>).

score 2 · Answer 2 · answered Apr 24 '19 at 10:43

you have to put group by as you used aggregate function count

SELECT 
       count(*) as t
      ,t1.EMPLID
     ,t3.DNI
      ,t1.PUNCH_DTTM
      ,PUNCH_TYPE
      ,TCD_ID
      ,t2.PCADescripcion
      ,t2.DFIDescripcion
      ,t2.Alias
      ,t5.apellido
      ,t4.planta

  FROM [MatrizDB].[db_owner].[Historial_TRH] t1
      left join MatrizDB.db_owner.Dispositivos_TRH t2
        on t2.RefId = TCD_ID
     left join MatrizDB.db_owner.Pipolsoft_Legajos t3
        on t3.EMPLID = t1.EMPLID
     left join [MatrizDB].[dbo].[nomina_fase_two] t4
        on t4.dni_id = t3.DNI 
     left join [MatrizDB].dbo.v_nomina t5
        on t5.nroDocumento = t4.dni_id
  where  CONVERT(VARCHAR(25),PUNCH_DTTM,120) LIKE '2019-04-24%' 
         AND t4.dni_responsable = 30329134 and PUNCH_TYPE = 5
  group by t1.EMPLID
     ,t3.DNI
      ,t1.PUNCH_DTTM
      ,PUNCH_TYPE
      ,TCD_ID
      ,t2.PCADescripcion
      ,t2.DFIDescripcion
      ,t2.Alias
      ,t5.apellido
      ,t4.planta

How to fix this error "is invalid in the select list because it is not contained in..."

2 Answers2