SQL SERVER Filter

Question

SQL SERVER Filter

Navigation

#1 by (3 votes)

0

I have an exercise to do but I'm breaking my head here:

"SQL command that brings me the sum of the salaries received by employee and state in the year 2014 of the tables: SALARIES / COLLABORATORS / LOCAL_DE_TRABALHO".

I have already created and filled in all the tables, now I am 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 'COLLABORATORS.COLABORATORNAME' column is invalid in the select list because it is not contained in an aggregate function or in the GROUP BY clause.

mysql sql sql-server

asked by anonymous 02.07.2018 / 01:40

1 answer

How do I assign the last value of a vector to a variable? Counter adding an extra value in vector with pointer

score 3 · Accepted Answer

When you use a aggregation function in this way ( in this example the SUM ) you need to specify the criteria for the grouping to show in the result.

In your case, you want the sum of valor_pago per collaborator . The selection criterion 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 a year, otherwise the result will show a sum for each payment date.

So, your query 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)