Do not repeat data when doing a SELECT in MySql

Posted on

Question :

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?

    

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

    

Leave a Reply

Your email address will not be published. Required fields are marked *