JPQL JPA - Create a list grouped by date with the sum and profit of all items sold that day

Asked

Viewed 159 times

0

The SQL query below is returning a wrong result to the fieldEntrega. 

SELECT
DATE(p.dt_pedido) AS  barChartLabels,
COUNT( DISTINCT p.id) AS qtdPedido,
SUM((item.quantidade * item.preco) * p.taxa_compra) AS percentualCompra,
SUM(item.quantidade * item.preco) AS valoresTotal,
SUM(p.taxa_bairro) AS valorEntregaAS valorEntrega

FROM pedido p, item_pedido item
where (p.id = item.pedido_id)
GROUP BY DATE(p.dt_pedido)

Instead of simply doing the sum of the Order value.taxa_Bairro, it is multiplying this order.taxa_Bairro by the amount of items in each order and then adding up these results. How to make it only perform the summation and not the multiplication? Below the diagram:

inserir a descrição da imagem aqui

The idea is that after SQL correction I can convert it to JPQL. Grateful

sql jpa jpql

  • 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?

  • 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.

  • 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 of SUM(p.taxa_bairro) just put him in SELECT?

  • 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.

  • Thanks @Pedrogaspar, I will edit the question by observing the article you put.

  • @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.

  • But the link to the other table is on WHERE and not in the list of selected fields!

  • @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)

  • I suggest you add details about table columns in the question text. For example, what it contains p.taxa_compra?

Show 4 more comments

1 answer

2


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.

  • Ola Pedro, the problem I really reported is this, described by you. However, the code is giving an error "Error Code: 1054. Unknown column 'p.id' in 'Where clause" I have tried changing the alias 'p' to the table name, but the error continues. I am using Mysql.

  • @Joséluizgonzaganeto, I switched to FROM pedido p INNER JOIN (SELECT ..., see if it works now.

  • No, Pedro, it’s still the same flaw. Your line of reasoning is perfect, I just have to identify what in the syntax I should fit.

  • I made a new edition in the reply, see if now was OK.

  • @Joséluizgonzaganeto, is now working, I even put a link to SQL Fiddle, take a look and tell if it was OK there for you too.

  • Pedro, studying your code I managed to run, follows the updated code to you put in the answer. select 
DATE(pedido.dt_pedido ) AS barChartLabels ,
COUNT(pedido.id) AS qtdPedido,
SUM(itens.total * pedido.taxa_compra) as percentualCompra,
SUM(pedido.taxa_bairro) AS valorEntrega,
SUM(itens.total) as custoProdutos
from pedido
inner join 
( select pedido_id as pedido_id, SUM(item.quantity * item.preco) as total from item_request item group by item.pedido_id ) items on items.pedido_id = order.id group by DATE(request.dt_request )

  • Thank you for the support.

Show 2 more comments

Browser other questions tagged sql jpa jpql

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