After talking to the author through the comments, I realized that the problem occurred because when making a query with full Join (Retrieving Records from Multiple Tables | Mysql SQL Syntax and Use | Informit), the number of records covered was the number of records in the table item_pedido
(14 records).
In his example the return of the table pedido
, for orders made on the date 01/11/2018, brought 2 records:
+-----+---------------------+-------------+-------------+
| id | dt_pedido | taxa_bairro | taxa_compra |
+-----+---------------------+-------------+-------------+
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 |
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 |
+-----+---------------------+-------------+-------------+
And the return of the table item_pedido
, for the items related to those 2 orders, it brought 14 records:
+-----------+-------+------------+
| pedido_id | preco | quantidade |
+-----------+-------+------------+
| 227 | 35.00 | 1.00 |
| 227 | 25.21 | 1.00 |
| 227 | 16.10 | 1.00 |
| 227 | 23.10 | 3.00 |
| 227 | 2.00 | 2.00 |
| 227 | 6.00 | 2.00 |
| 227 | 1.00 | 2.00 |
| 227 | 33.55 | 2.00 |
| 227 | 21.22 | 1.00 |
+-----------+-------+------------+
| 228 | 20.12 | 2.00 |
| 228 | 11.10 | 2.00 |
| 228 | 21.22 | 2.00 |
| 228 | 13.22 | 1.00 |
| 228 | 25.21 | 3.00 |
+-----------+-------+------------+
Then, after the full Join of the two tables:
FROM pedido p, item_pedido item
WHERE p.id = item.pedido_id
The return would be a merge of the two results, ie 14 records:
+-------+---------------------+-------------+-------------+-----------+-------+------------+
| id | dt_pedido | taxa_bairro | taxa_compra | pedido_id | preco | quantidade |
+-------+---------------------+-------------+-------------+-----------+-------+------------+
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 35.00 | 1.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 25.21 | 1.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 16.10 | 1.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 23.10 | 3.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 2.00 | 2.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 6.00 | 2.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 1.00 | 2.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 33.55 | 2.00 |
| 227 | 2018-11-01 03:26:19 | 1.00 | 0.10 | 227 | 21.22 | 1.00 |
+-------+---------------------+-------------+-------------+-----------+-------+------------+
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 | 228 | 20.12 | 2.00 |
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 | 228 | 11.10 | 2.00 |
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 | 228 | 21.22 | 2.00 |
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 | 228 | 13.22 | 1.00 |
| 228 | 2018-11-01 03:39:09 | 1.00 | 0.10 | 228 | 25.21 | 3.00 |
+-------+---------------------+-------------+-------------+-----------+-------+------------+
Hence the return of the field SUM(p.taxa_bairro)
was '14.00', instead of the value '2.00' expected by the author.
To solve this, you can put the sum of the values of the order items in a subquery, thus:
SELECT
DATE(p.dt_pedido) AS barChartLabels,
COUNT(p.id) AS qtdPedido,
SUM(itens.valorTotalPedido * p.taxa_compra) AS percentualCompra,
itens.valorTotalPedido AS valoresTotal,
(SUM(itens.valorTotalPedido * p.taxa_compra) + SUM(p.taxa_bairro)) AS valorEntrega
FROM pedido AS p
INNER JOIN
(SELECT pedido_id, SUM(quantidade * preco) AS valorTotalPedido
FROM item_pedido
GROUP BY pedido_id
) AS itens
ON itens.pedido_id = p.id
GROUP BY DATE(p.dt_pedido)
Check the result on SQL Fiddle.
I have been observing that even though it is not written in the query, it is taking the value of the Order Tax and multiplying by the amount of items that it has in this request, and the correct is not to occur this multiplication. This multiplication cannot occur, how to do this?
– Gonzaga Neto
Try not to put code images, instead paste the code into the same question, as in the case of JSON and SQL code, it is easier for those who read your question and try to answer it. In the case of screenshots all right.
– Pedro Gaspar
Are you using Mysql? Why are you using
DISTINCT
to return the order date and the id Count? It would not be necessary for the date if you are already grouping by it, and for the id is also denationary, because the id should be unique. What is the return ofSUM(p.taxa_bairro)
just put him inSELECT
?– Pedro Gaspar
I suggest you take a look at this website help article: How to create a Minimum, Complete and Verifiable example. In your case, I think it would only be enough to post your SQL query, because that’s where the problem is and that’s where you want help. When you post a very large text like this, very full of details, it may be that fewer people read it altogether, so if it is not necessary, it is best to try to put as little as possible, enough to identify the problem.
– Pedro Gaspar
Thanks @Pedrogaspar, I will edit the question by observing the article you put.
– Gonzaga Neto
@Pedrogaspar, if I leave only SUM(p.taxa_Bairro) and remove all other SQL fields, besides removing the link with the other table it performs the calculation correctly, but if I enter the link with the daughter table, either this way or Join, it goes back to multiply by the lines of the items before adding.
– Gonzaga Neto
But the link to the other table is on
WHERE
and not in the list of selected fields!– Pedro Gaspar
@Pedrogaspar, what is happening is that because of the link he is repeating the request.taxa_Bairro by the same number of the quantity of items and then adding. It’s like before the SUM it replicates the line 14 (number of items) times. (I’m using Mysql)
– Gonzaga Neto
I suggest you add details about table columns in the question text. For example, what it contains
p.taxa_compra
?– José Diz