search database specific row to fill table

Question

I am working on a system for technical installation of internet and line, and I need to fill a table with an X number of installation by type.

So far so good, I’m making a count type and grouping to know how many installations of each type the technician made:

This is the result that my bank generates, but I need to get the result of a specific column row qtd to play on my table.

function relatorioOs() {
  var Dados = $("#list_os").serialize();
  $.ajax({
    type: "POST",
    crossDomain: true,
    url: bd+'/relatorio_data.php',
    dataType: "text",
    async: false,
    data:Dados,
    beforeSend: function (xhr) {
      app.dialog.preloader('Buscando os');
    },
    success: function (result) {
      var data = JSON.parse(result);
      console.log(data);
      var htmlrelatorio1 = "";
      var htmlrelatorio2 = "";
      var htmlrelatorio3 = "";
      var htmlrelatorioT = "";
      $.each(data, function (i, item) {
       htmlrelatorio1 = 
       ' <tr>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '<td class="numeric-cell">'+item.qtd+'</td>'+
       '</tr>';

       htmlrelatorioT = 
       ' <tr>'+
       '<td class="numeric-cell">'+item.total_os+'</td>'+
       '<td class="numeric-cell">'+item.total_pontos+'</td>'+
       '</tr>';

       $("#rel-os1").append(htmlrelatorio1);
       $("#rel-osT").append(htmlrelatorioT); 
     });
    },
    error: function () {
      app.dialog.preloader('Erro ao buscar os');
    },
    complete: function () {
      app.dialog.close();
    }
  });
};

This is the PHP code with the connections to the database:

<?php

include_once("conectar.php");

$dataInicio = $_POST['data-inicio'];
$dataFim = $_POST['data-fim'];

if (!$conexao) {
    die("Connection failed: " . $conexao->conexao_error());
}

$sql = "SELECT count(nome)AS total_os , SUM(pontuacao) AS total_pontos,tipo,count(tipo) as qtd  FROM dados_os where dados_os.data between '$dataInicio' and  '$dataFim' 
group by tipo" ;

if (mysqli_query($conexao, $sql)) {
    $result = mysqli_query($conexao, $sql);

    if ($result) {
        while ($row = $result->fetch_object()) {
            foreach ($row as $key => $col) {
                $array_linha[$key] = utf8_encode($col);
            }
            $array_resultado[] = $array_linha;
        }
        echo utf8_encode(json_encode($array_resultado));
    }
    mysqli_close($conexao);
} else {

    echo "Error: " . $sql . "<br>" . $conexao->conexao_error;
}

?>

Answer 1

Your explanation is a little confusing. But I’d do it in your image. With 2 or more Inner Join I think we can solve your case.