SQL Errror in group by [duplicate]

Question

SQL Errror in group by [duplicate]

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

Hello friends, I'm trying to run the following query

select
x.[Nombre de empleado],x.[Sueldo diario],x.[Sueldo integrado],x.Total,x.conteo
from(
select
    em.nombreLargo [Nombre de empleado],
    em.sueldoDiario [Sueldo diario],
    em.sueldoIntegrado [Sueldo integrado],
    count(distinct convert(varchar,bi.FechaVerificacion,111)) conteo ,
    CAST(em.sueldoDiario AS DECIMAL(18, 4)) + 
    CAST(em.sueldoIntegrado AS DECIMAL(18, 4)) [Total]
    --COUNT(bi.FechaVerificacion) [días laborados]

    from 
    sysCPEmpleados ce 
    inner join  sysCPCentroCosto cc on 
    ce.idSysCPCentroCosto = cc.idSysCPCentroCosto 
    and
    ce.idSysCPEmpresas = cc.idSysCPEmpresas
    inner join sysCPEmpleados em on
    em.idSysCPEmpresas = cc.idSysCPEmpresas
    inner join sysCPEmpleadosBiometricoDetalle bi on
    bi.idSysCPEmpleados = ce.idSysCPEmpleados


    where cc.idSysCPCentroCosto ='SBIB15C'          
    --and em.idSysCPPuestos = @idPuestos
    and em.idEstatus = 2
    ) as x
group by x.[Nombre de empleado],x.[Sueldo diario],x.[Sueldo integrado],x.conteo,x.Total

, but I do not know why he sent me this error:

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

Could someone tell me what is failing me?

sql sql-server

asked by E.Rawrdríguez.Ophanim 31.10.2018 в 18:43

source

2 answers

1

You must group the fields that are inside the subquery so that your code works, your code should remain that way I hope it works for you,

select
    x.[Nombre de empleado],
    x.[Sueldo diario],
    x.[Sueldo integrado],
    x.Total,
    x.conteo
from(
select
    em.nombreLargo [Nombre de empleado],
    em.sueldoDiario [Sueldo diario],
    em.sueldoIntegrado [Sueldo integrado],
    count(distinct convert(varchar,bi.FechaVerificacion,111)) conteo ,
    CAST(em.sueldoDiario AS DECIMAL(18, 4)) + 
    CAST(em.sueldoIntegrado AS DECIMAL(18, 4)) [Total]
    from sysCPEmpleados ce 
    inner join  sysCPCentroCosto cc on ce.idSysCPCentroCosto = cc.idSysCPCentroCosto 
    and ce.idSysCPEmpresas = cc.idSysCPEmpresas inner join sysCPEmpleados em on
    em.idSysCPEmpresas = cc.idSysCPEmpresas inner join sysCPEmpleadosBiometricoDetalle bi on
    bi.idSysCPEmpleados = ce.idSysCPEmpleados
    where cc.idSysCPCentroCosto ='SBIB15C'          
    and em.idEstatus = 2
    group by em.nombreLargo, em.sueldoDiario, em.sueldoIntegrado 
    ) as x
group by x.[Nombre de empleado],x.[Sueldo diario],x.[Sueldo integrado],x.conteo,x.Total

answered by 31.10.2018 в 20:10

Positioning stuck to the top inside a CSS div android coin converter

score 1 · Accepted Answer

When you use Count (), and you bring more records in addition to that, you need to group the other records, so you need a Group By in "x":

select
x.[Nombre de empleado],x.[Sueldo diario],x.[Sueldo integrado],x.Total,x.conteo
from(
select
    em.nombreLargo [Nombre de empleado],
    em.sueldoDiario [Sueldo diario],
    em.sueldoIntegrado [Sueldo integrado],
    count(distinct convert(varchar,bi.FechaVerificacion,111)) conteo ,
    CAST(em.sueldoDiario AS DECIMAL(18, 4)) + 
    CAST(em.sueldoIntegrado AS DECIMAL(18, 4)) [Total]
    --COUNT(bi.FechaVerificacion) [días laborados]

    from 
    sysCPEmpleados ce 
    inner join  sysCPCentroCosto cc on 
    ce.idSysCPCentroCosto = cc.idSysCPCentroCosto 
    and
    ce.idSysCPEmpresas = cc.idSysCPEmpresas
    inner join sysCPEmpleados em on
    em.idSysCPEmpresas = cc.idSysCPEmpresas
    inner join sysCPEmpleadosBiometricoDetalle bi on
    bi.idSysCPEmpleados = ce.idSysCPEmpleados


    where cc.idSysCPCentroCosto ='SBIB15C'          
    --and em.idSysCPPuestos = @idPuestos
    and em.idEstatus = 2
    group by em.nombreLargo,em.sueldoDiario,em.sueldoIntegrado
    ) as x
group by x.[Nombre de empleado],x.[Sueldo diario],x.[Sueldo integrado],x.conteo,x.Total