Add up final total value using group by Mysql?

Asked

Viewed 1,151 times

0

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

table vendor

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 consultation is as follows

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

I am using this php query, no while I get the values grouped by seller and to make the percentage I need to accumulate the accumulated value of each seller in a variable within the loop, but wanted it to be in the consultation itself to show the % already in front of the accumulated value, 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

  • To add it all up would not be like this: (SUM(Valor) - SUM(Desconto) - SUM(Deflacionado)) AS tt?

  • So as I use a group by, it does so the sum, however for each seller, and does not mount it, I want to know if I can make the total sum in this query without having to create subquerys?

1 answer

4


To reuse a query can use a @variavel next to Mysql instead of PHP:

I made an example with simple values, for you to understand the isolated use of the variable. then you have to adapt 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 on SQL Fiddle.

An alternative is to use the UNION with SUM, but then ends up processing twice 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 on SQL Fiddle.

In practice, I find it 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 is derisory the difference. Also, it avoids carrying an extra column in communication between My and PHP.

Browser other questions tagged php mysql

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