SQL-Select To calculate seller commission

Asked

Viewed 681 times

0

I need to calculate a seller’s salary + 10% on each service he performs.

tabelas

I thought about making a select with a COUNT and then doing the calculation. But I’m not getting it. Here’s what I tried, didn’t return right...

SELECT   count(a.codfunc),f.salario FROM funcionario f, atendimento a where f.salario=a.codfunc or a.codfunc='1' ;
sql

  • At each attending you want to increase 10% of the employee’s salary?

  • 2

    Please [Dit] the post making the rules clearer, preferably with an example of what should happen, especially about the 10% rule, as already asked by colleague @Andre, to avoid that answers are based on misinterpretation. In addition, it would be good to explain the logic of your attempt, so that it is easier to detail the specific part that is difficult to solve.

3 answers

-1


Perform the following SELECT: 

SELECT funcionario.CODFUNC, 
           funcionario.NOME, 
           funcionario.SALARIO,
           funcionario.SALARIO * 0.1 * (SELECT COUNT(*) FROM atendimento WHERE atendimento.CODFUNC = funcionario.CODFUNC) COMISSAO,
           funcionario.SALARIO + (funcionario.SALARIO * 0.1 * (SELECT COUNT(*) FROM atendimento WHERE atendimento.CODFUNC = funcionario.CODFUNC)) SALARIO_TOTAL
  FROM funcionario

Where the values of funcionario together with the comissao(given according to the number of calls made by each employee) and the salário total resulting from the sum of the salary and the commission of each official.

If you want to perform the query for a specific employee, simply add the clause WHERE with your code at the end of the above query, example below:

WHERE funcionario.cod = 1

  • Downvoter could explain why -1 or where the response could be improved?

-2

Good morning, is missing the GROUP BY clause in the query so it is giving error. Every time you make a Count or Sum grouping you should use a GROUP BY.

See how it should look.

SELECT   
count(a.codfunc),
f.salario 
FROM 
funcionario f, atendimento a 
where 
f.salario = a.codfunc 
or 
a.codfunc = '1' group by f.salario ;

  • There are two employees registered and only one of them has 5 calls. This select brought the two employees and as if the two had 5 calls.

-2

Try to calculate with a subquery the number of calls for each employee.

SELECT funcionario.codfunc, 
       funcionario.nome, 
       (funcionario.salario * 0.1 * (SELECT count(*) FROM atendimento WHERE atendimento.codfunc = funcionario.codfunc)) AS comissao 
FROM funcionario;

  • With this select it returns the two employees, in which part I should specify the employee code. To return one at a time?

  • With this SELECT it returns all existing employees in its working table with the respective commission calculation. If you do not want all employees add to the end a private WHERE specifying the employee(s) (s) desired).

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.