Mysql query with 3 different criteria within the same table in the DB

Question

I need to create a query Mysql/PHP with 3 different criteria, but within the same BD table.

Tabela Negociações (Nome: tbl_neg)
ID    STS     VALOR
 1     1    R$23.000,00
 2     2    R$60.000,00
 3     1    R$10.000,00
 4     1    R$50.000,00
 5     6    R$20.000,00
 6     2    R$88.000,00
 7     6    R$15.000,00

I need to make a query in this table, but in the status column (name: Sts) there will always be three criteria, quote 1, 2 and 6, that I need to separate them and then add them up.

I don’t know if you can understand my "question". I’m thinking of something like a Join in the same table, because I’ve been doing a search on Dad (Google) and I know it exists. Would it be possible to use something like:

SELECT * FROM tb_neg a 
INNER JOIN tb_neg b ON a.stsneg = '1' AS analise 
INNER JOIN tb_neg c ON a.stsneg = '2' AS aprovado 
INNER JOIN tb_neg d ON a.stsneg = '6' AS reprovado

And then: (take each value)

$vranalise = $row["analise"];
$vraprovado = $row["aprovado"];
$vrreprovado = $row["reprovado"];

And then: (Imaginary Excel type: take the sub-total of each status)

$totalanalise = SOMA($vranalise);
$totalaprovado = SOMA($vraprovado);
$totalreprovado = SOMA($vrreprovado);

And then: (take total value)

$vrgeral = ($totalanalise + $totalaprovado + $totalreprovado);

I know it’s kind of imaginary, but I don’t know how to do it like you do and "catch" the hell out of it! ?

Answer 1

Buddy, it can be done using a pivot (turn the rows into columns).
If you want the select bring you 3 columns analise, aprovado and reprovado with the summed values, use this select:

SELECT SUM(IF(sts = 1, valor, NULL)) AS 'analise',
       SUM(IF(sts = 2, valor, NULL)) AS 'aprovado',
       SUM(IF(sts = 6, valor, NULL)) AS 'reprovado'
FROM tbl_neg;

The output of this select for the example you entered is:

analise    aprovado     reprovado  
83000       148000        35000

Now if you want the select to swallow the same 3 columns but without adding, remove the SUM of each row and you will get the result:

analise     aprovado    reprovado
23000       (null)      (null)
(null)      60000       (null)
10000       (null)      (null)
50000       (null)      (null)
(null)      (null)      20000
(null)      88000       (null)
(null)      (null)      15000

You can test the example above here

Edited
As requested by the author, the complete code for solving the problem using the above example as a basis is:
1 - Change the mask so that the currency values are saved in the bank in American format, from R$15,000.55 to 15000.55

// --------- SOMA DE VALORES - TOTAL - PORCENTAGEM ------------
$sqlnegvr = mysqli_query($conn, "SELECT SUM(IF(stsneg = '1', vrpro, NULL)) AS 'analise', SUM(IF(stsneg = '2', vrpro, NULL)) AS 'aprovado', SUM(IF(stsneg = '6', vrpro, NULL)) AS 'reprovado' FROM tb_neg");
$rowvr = $sqlnegvr->fetch_assoc();
$aprovado = $rowvr["aprovado"];
$analise = $rowvr["analise"];
$reprovado = $rowvr["reprovado"];

//----- VALORES SEPARADOS
$vra = number_format($aprovado, 2,",",".");
$vraprovado = "R$ " . $vra;
$vrn = number_format($analise, 2,",",".");
$vranalise = "R$ " . $vrn;
$vrr = number_format($reprovado, 2,",",".");
$vrreprovado = "R$ " . $vrr;

//----- SOMA TUDO
$somat = ($vrn + $vra + $vrr);
$vrgt = number_format( $aprovado + $analise +$reprovado, 2,",",".");
$vrgeral = "R$ " .  $vrgt;

//----- PORCENTAGEM DE ANÁLISE
$ptvra = ($vra * 100) / $somat;
$nwpta = round($ptvra, 1);
$ptvra  = str_replace('.', ',', $nwpta);
$pcvag = str_replace('.', '', $nwpta);
$ptvrag = $pcvag;

  //----- PORCENTAGEM DE APROVADO 
$ptvrn = ($vrn * 100) / $somat;
$nwptn = round($ptvrn, 1);
$ptvrn  = str_replace('.', ',', $nwptn);
$pcvng = str_replace('.', '', $nwptn);
$ptvrng = $pcvng;

  //----- PORCENTAGEM DE REPROVADO  
$ptvrr = ($vrr * 100) / $somat;
$nwptr = round($ptvrr, 1);
$ptvrr  = str_replace('.', ',', $nwptr);
$pcvrg = str_replace('.', '', $nwptr);
$ptvrrg = $pcvrg;