How to make a SELECT DISTINCT with COUNT in Mysql?

Asked

Viewed 616 times

0

So, I need to make a SELECT that searches the bank for information about all the activities of the companies and returns the sum of all the "QNTD_PROCESSOS" of that activity. I’m trying to make a select Die along with the distinct, but am not succeeding.

I’ll show you how far I’ve come and then tell you how I’d like you to stay. I will post the jQuery code but it is not very necessary to understand the question.

function preencher_empresas() {
  var conta; //Variável para controlar o número de empresas que vem do banco e para manipular o "loader" da página	

  $.ajax({
    //dataType: "json",
    type: "POST",
    url: "../banco/banco-vision/pagina-relatorios/preencher-relatorio.php",
    cache: false,

  }).done(function(data) {

    var relatorio = "";

    $("#registros-relatorios").empty(); //LIMPAR LISTA

    $.each($.parseJSON(data), function(chave, relat) {
      //CRIANDO AS LINHAS COM OS TD DA TABELA QUE SÃO O RESULTADO NA CONSULTA AO BANCO 

      relatorio += '<tr>';

      relatorio += '<td>' + relat.DEPARTAMENTO + '</td>';
      relatorio += '<td>' + relat.COD + '</td>';
      relatorio += '<td>' + relat.EMPRESAS + '</td>';
      relatorio += '<td>' + relat.TRIBUTACAO + '</td>';
      relatorio += '<td>' + relat.TIPO_ATIVIDADE + '</td>';
      relatorio += '<td>' + relat.QNTD_PROCESSOS + '</td>';

      relatorio += '</tr>';


    });

    $('#registros-relatorios').html(relatorio);

    $("div#loading-relatorio").hide();

  }).fail(function() {
    alert('Falha na listagem dos usuários');
  }).always(function() {});
}
<?php

	//ARQUIVO CRIADO PARA PREENCHER A TABELA COM DADOS DO BANCO AO CARREGAR A PÁGINA

	date_default_timezone_set('America/Sao_Paulo');
	
	require_once("../../conexao/conexao-com-banco.php"); // CHAMANDO O ARQUIVO DE CONEXÃO AO BANCO
	
	// CONSULTA GERAL DO BANCO  QUE RETORNA O RESULTADO DA CONSULTA DA PÁGINA INTERFACE.PHP 
		
	session_start();	
	
	
	$departamento_usuario = $_SESSION["departamento-usuario"];
			
	$select_relatorio = "SELECT DEPARTAMENTO, RESPONSAVEL, COD, EMPRESAS, TRIBUTACAO, TIPO_ATIVIDADE, QNTD_PROCESSOS FROM tbl_atividades WHERE DEPARTAMENTO = '$departamento_usuario' ORDER BY EMPRESAS";
	$lista_relatorio = mysqli_query($conecta, $select_relatorio);
			
	if(!$lista_relatorio)
	{
		die("Erro no Banco - Erro no select na tabela tbl_atividades");
		exit;
	}
	
	$retorno_relatorio = array();
	while($linha_relatorio = mysqli_fetch_object($lista_relatorio))
	{				
			
		$retorno_relatorio[] = $linha_relatorio;
				
	} 	
	
	
	//JSON QUE VAI PARA O RETORNO DO AJAX COM DADOS DA CONSULTA DO SELECT
	echo json_encode($retorno_relatorio);		

?>

IMAGE OF THE DESCRIPTION OF THE TABLE IN WHICH THE QUERY IS MADE.

inserir a descrição da imagem aqui

IMAGE OF HOW IT IS COMING OUT: inserir a descrição da imagem aqui

If you notice, some activities repeat themselves. I would like them not to be repeated and there in the TD "Executed Processes" shows the sum of all the activities that were equal (of the same type, of the same company). (I don’t know if I could be clear).

  • I tried to do it with a SELECT DISTINCT, but I was unsuccessful. I managed to make sure that the activities do not repeat themselves, but I could not see a way to show the sum of these activities.

  • If you can help me, I’d appreciate it.

  • Have you tried using the GROUP BY, but the DISTINCT would have to work, maybe remove PHP and JS from the question and put the database structure and query, the question gets better

  • @Leonardobarros , how would this query look? I will put the table structure in which I make the query. The bank was not I who made it. The table is kind of unorganised, but it’s functional.

  • I had expressed myself badly. In fact I would like to get the sum of the amount of Processes of the repeating activities.

2 answers

1


If you Use COUNT and DISTINCT will generate the value of 1 for each line.

Do something like this:underlined text

SELECT 
    DEPARTAMENTO, 
    RESPONSAVEL, 
    COD, 
    EMPRESAS, 
    TRIBUTACAO, 
    TIPO_ATIVIDADE,
    SUM(QNTD_PROCESSOS) AS QNTD_PROCESSOS
FROM tbl_atividades 
WHERE DEPARTAMENTO = '$departamento_usuario' 
GROUP BY
    DEPARTAMENTO, 
    RESPONSAVEL, 
    COD, 
    EMPRESAS, 
    TRIBUTACAO, 
    TIPO_ATIVIDAD
ORDER BY 
    EMPRESAS

In Group you set up the organization you need, it will gather everything that is the same, then only adjust in Select, if you will do COUNT, SUM, AVG, or whatever is most convenient for solving your problem.

  • I had expressed myself badly. In fact I would like to get the sum of the amount of Processes of the repeating activities.

0

Good day, Try:

$select_relatorio = "SELECT TIPO_ATIVIDADE, count(TIPO_ATIVIDADE) as 'contagem' FROM tbl_atividades WHERE DEPARTAMENTO = '$departamento_usuario' group by TIPO_ACTIVIDADE";
  • But I need the other fields to fill the table that is in HTML. They wouldn’t have to be in this SELECT ?

  • I had expressed myself badly. In fact I would like to get the sum of the amount of Processes of the repeating activities.

  • In this case instead of Count(TIPO_ACTIVIDADE) puts Count(QNTD_PROCESSOS). And also add the remaining fields to select, placing them also in group by

  • You’d have to use SUM instead of COUNT.

Browser other questions tagged

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