I have the Product table with the product name and ID then also have the receipt_product table with product ID and receipt ID.

And finally, have the receipt table with the amount and the receipt ID. So I've tried to retrieve all the products and their total sales in the result.

This is my query and I know that my query is wrong and need to do something to make it correct.

Tables

select 
    p.name, sum(total) as total sales 
from 
    receipt_product rp 
right join 
    product p on p.product_id = rp.product_id 
inner join 
    receipt r on rp.receipt_id = r.receipt_id 
group by 
    p.name 
having 
    total_sales < 100

CodePudding user response：

You have the price of individual product and the quantity sold . So why are you taking the receipt table in consideration.

Try something like this.

select sum(p.price * r.quantity_sold) as "Total Sales",p.name 
from product p join receipt_product r
on p.product_id = r.product_id 
group by p.name;