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

Question

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

Navigation

#1 by (1 votes)

0

The SQL query below is returning a wrong result for the Delay value field.

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 summing the value of the Order.Time_Boirro, he is multiplying this order.Time_Boirro by the quantity of items of each order and then adding these results. How do you make it only sum and not multiplication? Below the diagram:

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

sql spring-data jpa jpql

asked by anonymous 03.11.2018 / 23:24

1 answer

Laravel array session overwriting How to list in Windows Explorer the Linux / Ubuntu www directory that runs on the Windows 10 Windows-to-Linux (WSL) Subsystem? [closed]

score 1 · Accepted Answer

After talking with the author through the comments, I realized that the problem happened because when doing a query with full join (Retrieving Records from Multiple Tables | MySQL SQL Syntax and Use | InformIT , the number of records traversed was the number of records in the item_pedido (14 records).

In his example the return of the pedido table, for requests made on 01/11/2018, had 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 item_pedido table, for the items related to those 2 requests, had 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 junction 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 |
+-------+---------------------+-------------+-------------+-----------+-------+------------+

So the return of the SUM(p.taxa_bairro) field was '14 .00 ', rather than the' 2.00 'expected by the author.

To solve this, you can put the sum of the values of the order items in a subquery , like this:

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 out the result in SQL Fiddle .