SQL SERVER Filter

Question

I have an exercise to do but I’m cracking my head here:

"SQL command that brings me the sum of the salaries received per employee and status in the year 2014 of the tables: SALARIES / EMPLOYEES / LOCAL_DE_TRABALHO".

I’ve already created and filled all the tables, now I’m trying to make the query.

SELECT SUM(VALOR_PAGO) as total_pago, NOME_COLABORADOR as colaborador,
       DATA_PAGAMENTO as Data_pagamento
from COLABORADORES   Inner join SALARIO
on SALARIO.ID_COLABORADOR = COLABORADORES.ID_COLABORADOR 
WHERE YEAR(DATA_PAGAMENTO) = 2014

Error:

The column 'CONTRIBUTORS.NOME_COLABORADOR' is invalid in the selection list because it is not contained in an aggregation function or in the GROUP BY clause.

Answer 1

When you wear one aggregation function in this way (in this example the SUM) you need to specify what the grouping criteria are to show in the result.

In your case, you want the summing up of valor_pago per employee. The selection criteria is the year of the payment date, but if you want to display it in the resultset too, it will have to be part of the grouping and as the same year, otherwise the result will show a sum for each payment date.

So your consultation should look like this:

SELECT 
    SUM(valor_pago) AS total_pago, 
    nome_colaborador AS colaborador, 
    YEAR(data_pagamento) AS ano_data_pagamento
FROM colaboradores 
    INNER JOIN salario ON salario.id_colaborador = colaboradores.id_colaborador
WHERE 
    YEAR(data_pagamento) = 2014
GROUP BY 
    nome_colaborador, 
    YEAR(data_pagemento)