SQL problem to add rows SQL SERVER

Question

SQL problem to add rows SQL SERVER

Navigation

#1 by (1 votes)

0

I need to add the salaries of the next query I have and sort them by description.

select distinct 

    cp.descripcion,
    cp.idSysCPPuestos,
    em.sueldoDiario,
    em.sueldoIntegrado,
    em.idSysCPCentroCosto
    --,sum(CAST(em.sueldoDiario AS DECIMAL(18, 4)))+sum(CAST(em.sueldoIntegrado AS DECIMAL(18, 4)))



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 sysCPPuestos cp on
    em.idSysCPPuestos = cp.idSysCPPuestos

    where cc.idSysCPCentroCosto = 'y'
    and em.idSysCPEmpresas = 'x'

this is what I have and what gives me back up here all right, but if I try to make the commented sum, it returns me the following

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

Someone could instruct me how to do this operation correctly, what I need is to add the amounts where the description of cc.description is the same. A thousand thanks.

mysql sql sql-server

asked by E.Rawrdríguez.Ophanim 23.10.2018 в 17:31

source

1 answer

Re-execute TRY if CATCH gives error How to delete a record from a table with Laravel?

score 1 · Answer 1

Whenever you do a columna1, sum(columna2) you'll have to add the group by columna1 clause

Returns the sum of all values or only the DISTINCT values of the expression. SUM can only be used with numeric columns. NULL values are omitted.

More info on SUM ( Transact-SQL)

select distinct 

    cp.descripcion,
    cp.idSysCPPuestos,
    em.sueldoDiario,
    em.sueldoIntegrado,
    em.idSysCPCentroCosto,
    sum(CAST(em.sueldoDiario AS DECIMAL(18, 4)))+sum(CAST(em.sueldoIntegrado AS DECIMAL(18, 4)))

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 sysCPPuestos cp on
       em.idSysCPPuestos = cp.idSysCPPuestos

    where cc.idSysCPCentroCosto = 'y'
       and em.idSysCPEmpresas = 'x'
    group by cp.descripcion,
       cp.idSysCPPuestos,
       em.sueldoDiario,
       em.sueldoIntegrado,
       em.idSysCPCentroCosto;