SQL Compute the price of cart in one request

Question

Hello,

Typically, I have a really simple cart with some items in it, with a quantity.

I wish to do something like this :

SELECT SUM(price * quantity) as total FROM products WHERE product_id IN (1,2,3,4);

But how can I bind the quantity with the product_id since quantity is not in the database ?

Is there any other way to do this with a low SQL cost ?

Answer 1

Maybe you can just return the list of price for a bunch of products and then multiply this list one by one with the attached quantity.

SELECT product_id, price FROM products WHERE product_id IN (1,2,3,4);

Then take your n-uplet out and multiply/sum with the quantities (being in an array or list, like the same list you get from your IN clause in Sql)