-1
I need to list the 10 best selling products from a table using Hibernate.
The table item_sale has the following columns:
- amount
- id_product
- valor_unitario
- value_sub_Total
1 answer
6
The best-selling items would be those most present in the item_sale table. In this case, you would only need to create the SQL query by sorting the items by the sum of quantity per product, i.e., by the number of occurrences of a product considering its quantity in the item_sale table. The sql would be:
select sum(quantidade), id_produto from item_venda group by id_produto
order by sum(quantidade) desc;
Okay, now you have the best-selling product ids in descending order (the best-selling first, the least-sold after). Now, if you need other product information from the best seller just create a subquery to bring them based on the ids that you recovered:
select * from produto p join
(select sum(quantidade), id_produto from item_venda group by id_produto
order by sum(quantidade) desc) itensMaisVendidos on (p.id = itensMaisVendidos.id_produto)
Okay, what was done in the above query was to first bring all the products and then account for the sale of each of them through the sum and group by. Using Join the product id is compared with the most sold product id, so no repeat results will appear due to the Cartesian product made through Join between the tables.
Browser other questions tagged java hibernate postgresql criteria hsqldb
You are not signed in. Login or sign up in order to post.
But wait, see if I list only by the amount of occurrence of the id_product the query would be mistaken, for example: the product x, occurs 5 times in the item_sale table, but the product y occurs only 1 time and has the amount == 6, see that the best selling product was product y and not the x, as I could do this consultation taking into account also the quantity sold.
– Alysson Oliveira
+1 you’re a beast! = D
– viana
Ahhh, if you have quantity there you exchange Count(*) for sum(quantity). Simple like this :)
– Giuliana Bezerra
Thank you very much :D
– Alysson Oliveira
It worked correctly, but I need to do it using Hibernate, I tried using Projections but without success, could you help me? Thanks in advance...
– Alysson Oliveira
Boy, I think I better use createSQLQuery (http://www.tutorialspoint.com/hibernate/hibernate_native_sql.htm) because subquery with HQL is complicated, nor do I know how it would be done in this case.
– Giuliana Bezerra