Get the total sum of 2 multiplied columns SQL

Question

I have these 3 tables:

Bill:

idBill

Products:

idProduct
price

BillProducts (that connects the tables above):

idBill
idProduct
quantity

Now let's say I wish to get the total price of a certain bill identified by its ID

I would need to multiply the columns of Products.Price by BillProducts.quantity, get its result and sum all the others products in that idBill

Can you guys help me writing that query?

Answer 1

You would do:

select sum(p.price * bp.product)
from billproducts bp join
     products p
     on bp.idproduct = p.idproduct
where idbill = <idbill>;

You can get this for all bills using group by:

select idbill, sum(p.price * bp.product)
from billproducts bp join
     products p
     on bp.idproduct = p.idproduct
group by idbill;

Notes:

• You do not need to join the bills table. All the information you need is in the other two tables.
• You do not need a subquery.
• When you define table aliases, they should be abbreviations for the table names, so the query is easier to follow.

Answer 2

SELECT SUM(QUANTxPRICE) AS SUMED, IDBILL FROM (
SELECT (A.QUANTITY * B.PRICE) AS QUANTxPRICE,a.IDBILL
FROM BILLPRODUCTS AS A
JOIN PRODUCTS AS B ON 
A.IDPRODUCT = B.IDPRODUCT
JOIN BILL AS C
ON A.IDBILL = C.IDBILL
) AS X
GROUP BY IDBILL