Do not repeat data when doing a SELECT in MySql

Question

Do not repeat data when doing a SELECT in MySql

Navigation

#1 by (1 votes)

3

What I want is this. I am working on a system, which has registered an employee and days in which he worked on the month. So far so good, but it happens that in some cases, the employee has 2 or more records in the same month, for example, I registered, between May 21 and 25, and May 28 and 30. So in my table you have the following records:

1 - Funcionario1 - 5 (dias) - 5 (mês de maio)
2 - Funcionario1 - 3 (dias) - 5 (mês de maio)

Now, what I need is to calculate the total number of days in the middle of May that this employee worked. I get more or less with this code:

for($i=0; $i<$mesatual; $i++) {
            echo "<h2>Plantões / ".$meses[$i]."</h2><br><br>";

            $query = "SELECT diasplantao.*, funcionarios.nome, funcionarios.atribuicao FROM diasplantao "
                    . "INNER JOIN funcionarios ON (diasplantao.funcionario_id = funcionarios.funcionario_id) WHERE (diasplantao.mes = $i+1) "
                    . "ORDER BY funcionarios.atribuicao DESC";

            $resultado = mysql_query($query);

            while($mostrar=mysql_fetch_array($resultado)) {
                echo "<b>Nome:</b> ".$mostrar['nome']." -- ";
                echo "<b>Atribuição:</b> ".$mostrar['atribuicao']." -- ";
                echo "<b>Mês:</b> ".$mostrar['mes']." -- ";
                echo "<b>Dias:</b> ".$mostrar['dias']."<br><br>";

            }
        }  
    ?>

The code displays the month, and just below the days the employees worked on that month. But it returns me this in the month of March for example.

Nome: George Cruijff Sales da Costa -- Atribuição: Analista -- Mês: 3 -- Dias: 12

Nome: George Cruijff Sales da Costa -- Atribuição: Analista -- Mês: 3 -- Dias: 10

How do I not repeat the name, and how best to calculate the total days? I was thinking of putting just one auxiliary variable and adding, but the name will keep repeating.

Does anyone shed any light on how best to do this?

php mysql sql-select

asked by anonymous 19.05.2014 / 21:59

1 answer

ORA-00907: right parenthesis not found Drag View to open new Activity

score 1 · Accepted Answer

You should use SUM to add the days and GROUP to bring the clustered lines

SELECT diasplantao.mes, 
       SUM(diasplantao.dias) as dias,
       funcionarios.id,
       funcionarios.nome, 
       funcionarios.atribuicao 
FROM 
    diasplantao 
    INNER JOIN funcionarios ON (diasplantao.funcionario_id = funcionarios.funcionario_id) 
WHERE 
    (diasplantao.mes = $i+1)
GROUP BY
    funcionarios.id, diasplantao.mes, funcionarios.nome, funcionarios.atribuicao 
ORDER BY 
    funcionarios.atribuicao DESC