0
I need to create two selects. One for the total and one for bringing all fields. How do I put 2 selects in the field creation query?
I need him to add this total:
1 answer
0
After suggestion of subselect the query was like this:
SELECT pedidos_id , orders.cliente_id , orders.product_id , clients.name AS customer name products.name AS product name , Preco as preco_produto, pedidos.data , pedidos.frete , Orders.Quantity, Orders.Total, (SELECT SUM(Orders.Total) FROM orders) AS sub_total FROM orders INNER JOIN customers ON orders.cliente_id = customers.clientes_id INNER JOIN products ON orders.producto_id = products.products_id GROUP BY ordering.pedidos_id ORDER BY customers.name
Browser other questions tagged jasper jaspersoft-studio
You are not signed in. Login or sign up in order to post.
create a select only where you bring all the fields and together a Count to bring the total of what you want
– R.Santos
Post the SQL command you need
– R.Santos
@R.Santos I need to make a sum of those values there. I have tried with sum, but it only returns a single value.
– Felipe Michael da Fonseca
Post the SQL command ,not the image so it is easier to help
– R.Santos
SELECT SUM(orders.total), pedidos_id , orders.cliente_id , orders.product_id , customers.AS name_customer name, products.AS name_product name, products.price as preco_product, orders.date , orders.shipping , orders.quantity, orders.total FROM orders INNER JOIN customers ON orders.cliente_id = customers.clientes_id INNER JOIN products ON orders.product_id = products.products_id GROUP BY orders.pedidos_id ORDER BY customers.name;
– Felipe Michael da Fonseca
In case you want the total value to be the same on all lines?
– R.Santos
Yes I want it to show the total and create another column with subtotal. But I thought I would do different in another query. That’s why I need to know how to do another select on that Jasper. That would make it easy to report. Type made a select for all joins and then another to bring the total values of the total column.
– Felipe Michael da Fonseca
Try a subselect to bring in the total value, I for example have a table
estoquewhere it has the code of the supply its serial number and the unitary value of each one, I did so:select codigosuprimento, numeroserie, valorunitario, (SELECT SUM (valorunitario)
 FROM public.estoque where usado = '0' and codigosuprimento = 155 group by codigosuprimento) as total FROM public.estoque where usado = '0' and codigosuprimento = 155;to bring the total value of this supply with its unitary value– R.Santos
You don’t have to. it worked. This was the query: SELECT pedidos_id , orders.cliente_id , orders.product_id , customers.AS name_customer name, products.AS name_product name, products.price as preco_product, orders.date , orders.freight , orders.quantity, orders.total, (SELECT SUM(orders.total) FROM orders) FROM orders FROM orders INNER JOIN customers ON orders.cliente_id = customers.clientes_id INNER JOIN products ON orders.product_id = products.products_id GROUP BY orders.pedidos_id ORDER BY customers.name
– Felipe Michael da Fonseca
Ah good that it worked, I posted an answer so that if someone has the same need have more facility to find the answer
– R.Santos