Add final total value using group by MySql?

Question

Add final total value using group by MySql?

Navigation

#1 by (4 votes)

0

I have the Sales table and the seller table and would like to know the sales percentage of each salesperson.

table seller

Id  | Vendedor |
1   | Fulano   |
2   | Beltrano |

table request

Id    |  Id_Vendedor  | Valor    | Desconto  | Deflacionado
1     |  1            | 17800.00 | 800.00    | 452.00
2     |  1            | 11400.25 | 351.00    | 127.00
3     |  2            | 341.00   | 10.00     | 0.00

My query looks like this

SELECT SUM(Valor - Desconto - Deflacionado) as tt
INNER JOIN pedido ON vendedor.Id = pedido.Id_Vendedor
GROUP BY pedido.Id_vendedor

I'm using this php query, in the while I get the values grouped by salesperson and to make the percentage I need to accumulate the cumulative value of each vendor in a variable within the loop, however I wanted it to be in the query itself to show the% already in front of the accumulated amount, as shown in the table below.

Valor     |     Vendedor      |     %
27470.25  |     Fulano        |     98.81 %
331.00    |     Beltrano      |     1.19 %

<table border="1">
<thead>
<tr>
<th>Nome</th>
<th>Valor</th>
<th>%</th>
</tr>
</thead>
<tbody>
<tr>
<td>Fulano</td>
<td>R$ 27.470,25</td>
<td>98.81 %</td>
</tr>
<tr>
<td>Beltrano</td>
<td>R$ 331,00</td>
<td>1.19 %</td>
</tr>
</tbody>
<tfoot>
<tr>
<th>Total</th>
<th>27.801,25</th>
<th>100 %</th>
</tr>
</tfoot>
</table>

php mysql

asked by anonymous 04.06.2018 / 20:54

1 answer

How to declare and pass a decimal variable from XAML to C #? How to select the most used foreign key?

score 4 · Accepted Answer

To reuse a query you can use a @variavel on the MySQL side instead of PHP:

I made an example with simple values, so you understand the use of the variable alone. then you have to adapt it to your grouping.

  SET @total := 0;

  SELECT item, valor, @total := @total + valor AS subtotal
  FROM   docs
  ORDER BY coluna_que_garante_ordenacao_correta

UNION

  SELECT 'TOTAL', @total, '';

See working in SQL Fiddle .

An alternative is to use UNION with SUM , but then it ends up processing double the DB:

  SELECT item, valor, @total := @total + valor AS subtotal
  FROM   tabela
  ORDER BY coluna_que_garante_ordenacao_correta

UNION

  SELECT 'TOTAL', SUM( valor ) AS total, ''
  FROM   tabela

See working in SQL SQL Fiddle .

In practice, I think it's much better to leave the variable in PHP itself because you can better control the separate formatting in HTML, and in terms of speed and memory the difference is paltry. Also, avoid carrying an extra column in the communication between My and PHP.